Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate Totals for ID numbers that have more than one entry Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, February 10, 2012 9:18 AM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 7:05 AM Points: 7, Visits: 278
 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
Post #1250398
 Posted Friday, February 10, 2012 11:09 AM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
 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
Post #1250469
 Posted Tuesday, February 14, 2012 2:56 AM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 7:05 AM Points: 7, Visits: 278
 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
Post #1251747
 Posted Tuesday, February 14, 2012 7:23 AM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
 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
Post #1251901
 Posted Thursday, April 12, 2012 3:16 AM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 7:05 AM Points: 7, Visits: 278
 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
Post #1282210

 Permissions