48231

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

  • Strange ways comparisons occur
  • Best way to remove thousand separators from string amount using a regex
  • Multi-line JSON read using Apache PIG
  • python regex split string while keeping delimiter with value
  • How to retrieve a specific string from a specific list from a file with JSON in Python
  • Null ImageView Reference
  • How to check if a string contains any string of a column in MySQL and vice versa?
  • Python adding lots of things to PATH. How do I stop?
  • Getting syntax error in mysql-php. You have an error in your SQL syntax;
  • Mercurial hook to set policy on tag names
  • include dlls in visual studio c++ 2008
  • Binary trees in scheme
  • Laravel at least one field is required
  • Loading Bitmap to ImageView from URL in android
  • What is the difference between a “service account” and an “installed application”?
  • IE10 strips out hashtag from the URL
  • Need code translation from VB to C#
  • Change Checkbox value without raising event
  • Synchronize windows folders
  • Is there a way to dynamically embed PDF Files in a JSP pulled from the file system?
  • Retrieving specified columns from a list of csv files to create a data data frame in R
  • Primefaces ManyCheckbox inside ui:repeat calls setter method only for last loop
  • using System.Speech.Synthesis with Windows10 universal app (XAML-C#)
  • Check all values in string[] for length?
  • Image map in Flex
  • Build Successful but not running on simulator
  • Django model inheritance, filtering models
  • Jquery popup on mouse over of calendar control
  • pyodbc doesn't report sql server error
  • Check for zero lines output from command over SSH
  • How to clear text inside text field when radio button is select
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Can I check if a recipient has an automatic reply before I send an email?
  • Recording logins for password protected directories
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Splitting given String into two variables - php
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Check if a string to interpolate provides expected placeholders
  • How to extract text from Word files using C#?