Merge multiple records into one row in a table

I have a table which has multiple records of the same sales agent id but different sales amount. How can I delete the multiple rows and just have the aggregate of the total value.

Let us for example assume the table structure as follows -

SalesAgentId, SalesAgentName, SalesAmount 111 , John Doe , 8437.00 112 , John O Connor , 5849.00 111 , John Doe , 438.00 112 , John O Connor , 1234.00

What I would want is (even if it is into a different table) the following -

SalesAgentId, SalesAgentName, SalesAmount 111 , John Doe , 8875.00 112 , John O Connor , 7083.00

Can we do this using SQL statements or should it be an Stored Procedure only? If it is using an SP, do we have to iterate through each of the records...check if it is always there, if so, just add the SalesAmount field to the existing table and implement it?

Answer1:

SELECT SalesAgentId, SalesAgentName, SUM(SalesAmount) AS SalesAmount INTO #AggSales FROM Sales GROUP BY SalesAgentId, SalesAgentName; TRUNCATE TABLE Sales; INSERT INTO Sales SELECT * FROM #AggSales; DROP TABLE #AggSales;

Answer2:

This is the easiest way I can think of doing it:

create table #tempSales (salesagentid int, salesagentname varchar(50), salesamount money) go insert into #tempSales select salesagentid, salesagentname, sum(salesamount) from salesTable group by salesagentid, salesagentname go select * from #tempSales

Answer3:

SELECT SalesAgentID, SUM(SalesAmount) FROM Sales GROUPBY SalesAgentID

But there's something wrong here... Why your table have BOTH SalesAgentId and SalesAgentName?

It should contain only the ID, the name should be in a SalesAgent table. Then you would retrieve the name with a join

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Merge multiple records into one row in a table