## Calculate Totals for ID numbers that have more than one entry

 Author Message emmettjarlath Grasshopper Group: General Forum Members Points: 13 Visits: 281 Hi,I have a table with about 700 rows, 100 ID numbers that have more than one entry. I need to add the amount for each ID number, who have the same date, to get the total amount. For example:ID Dated Amount1 2012-02-03 00:00:00.000 100.001 2012-02-03 00:00:00.000 25.001 2012-02-03 00:00:00.000 125.002 2012-02-04 00:00:00.000 50.002 2012-02-04 00:00:00.000 15.003 2012-02-07 00:00:00.000 190.003 2012-02-07 00:00:00.000 210.003 2012-02-07 00:00:00.000 60.003 2012-02-07 00:00:00.000 40.00The result should look like this:ID Dated Amount1 2012-02-03 00:00:00.000 250.002 2012-02-04 00:00:00.000 65.003 2012-02-03 00:00:00.000 500.00Any suggestions would be a great help. Thank you in advance,emmettjarlath bitbucket-25253 SSCertifiable Group: General Forum Members Points: 7821 Visits: 25280 Try this:`SELECT ID,Dated,SUM(Amount) FROM #T GROUP BY ID,Dated ORDER BY ID `Of course alter the FROM #T to FROM substitue your tables nameMy resutl:1 2012-02-03 00:00:00.000 250.002 2012-02-04 00:00:00.000 65.003 2012-02-07 00:00:00.000 500.00 If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read emmettjarlath Grasshopper Group: General Forum Members Points: 13 Visits: 281 Hi bitbucket-25253,Thanks for your help. Your sugegstion worked very well. All I had to do was alter the code to my requirements and hey presto, SUCCESS!Unfortunately a new challenge has arised. The table I am pulling the information from has duplicate distinct unit id numbers, for example.unit_id total1 1551 399I do not want to view both. The unit id numbers I want to view are also in another table I can pull from. I have messed around with a join but to no avail. The unit id number in the other table is actually called analysis_code1. Is there a way I can define the unit id I want?Thanks again,emmettjarlath bitbucket-25253 SSCertifiable Group: General Forum Members Points: 7821 Visits: 25280 If I understand your question correctly, see if something like this will work for you.`CREATE TABLE T2000(ID Int,Dated DATETIME,Amount DECIMAL(10,2))INSERT INTO T2000SELECT 1, '2012-02-03 00:00:00.000', 100.00 UNION ALLSELECT 1, '2012-02-03 00:00:00.000', 25.00 UNION ALLSELECT 1, '2012-02-03 00:00:00.000', 125.00 UNION ALLSELECT 2, '2012-02-04 00:00:00.000', 50.00 UNION ALLSELECT 2, '2012-02-04 00:00:00.000', 15.00 UNION ALLSELECT 3, '2012-02-07 00:00:00.000 ',190.00 UNION ALLSELECT 3, '2012-02-07 00:00:00.000', 210.00 UNION ALLSELECT 3, '2012-02-07 00:00:00.000', 60.00 UNION ALLSELECT 3, '2012-02-07 00:00:00.000', 40.00CREATE TABLE #X(ID Int,Dated DATETIME,Amount DECIMAL(10,2))INSERT INTO #XSELECT ID,Dated,SUM(Amount) FROM T2000 GROUP BY ID,Dated ORDER BY ID SELECT ID,Dated,Amount FROM #XCREATE TABLE Second(ID INT,Whoops VARCHAR(20)) --My mistake ID should have been analysis_code1INSERT INTO SecondSELECT 1,'YUP' UNION ALLSELECT 2,'Maybe' UNION ALLSELECT 3,'Yes' UNION ALLSELECT 4,'Nope'SELECT c.ID,c.Dated,c.Amount,s.WhoopsFROM #X cJOIN Second s ONc.ID = s.ID-- Result:1 2012-02-03 00:00:00.000 250.00 YUP2 2012-02-04 00:00:00.000 65.00 Maybe3 2012-02-07 00:00:00.000 500.00 Yes` If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read emmettjarlath Grasshopper Group: General Forum Members Points: 13 Visits: 281 Hi,I should have closed this topic off weeks ago. I solved the this issue. I am now able to total my figures from a table on one server and compare the totals to another table on a different server.Thank you for help, it really pointed me in the right direction.emmettjarlath