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.

Solution 1:

SELECT SalesAgentId, SalesAgentName, SUM(SalesAmount) AS SalesAmount
  INTO #AggSales
  FROM Sales
 GROUPBY SalesAgentId, SalesAgentName;

TRUNCATETABLE Sales;    

INSERTINTO Sales
SELECT*FROM #AggSales;

DROPTABLE #AggSales;

Solution 2:

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

createtable #tempSales (salesagentid int, salesagentname varchar(50), salesamount money)
go

insertinto #tempSales 
select salesagentid, salesagentname, sum(salesamount)
from salesTable
groupby salesagentid, salesagentname
go

select*from #tempSales

Solution 3:

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