February 10, 2012 at 9:18 am
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 Amount
1 2012-02-03 00:00:00.000 100.00
1 2012-02-03 00:00:00.000 25.00
1 2012-02-03 00:00:00.000 125.00
2 2012-02-04 00:00:00.000 50.00
2 2012-02-04 00:00:00.000 15.00
3 2012-02-07 00:00:00.000 190.00
3 2012-02-07 00:00:00.000 210.00
3 2012-02-07 00:00:00.000 60.00
3 2012-02-07 00:00:00.000 40.00
The result should look like this:
ID Dated Amount
1 2012-02-03 00:00:00.000 250.00
2 2012-02-04 00:00:00.000 65.00
3 2012-02-03 00:00:00.000 500.00
Any suggestions would be a great help.
Thank you in advance,
emmettjarlath
February 10, 2012 at 11:09 am
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 name
My resutl:
12012-02-03 00:00:00.000250.00
22012-02-04 00:00:00.00065.00
32012-02-07 00:00:00.000500.00
February 14, 2012 at 2:56 am
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 total
1 155
1 399
I 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
February 14, 2012 at 7:23 am
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 T2000
SELECT 1, '2012-02-03 00:00:00.000', 100.00 UNION ALL
SELECT 1, '2012-02-03 00:00:00.000', 25.00 UNION ALL
SELECT 1, '2012-02-03 00:00:00.000', 125.00 UNION ALL
SELECT 2, '2012-02-04 00:00:00.000', 50.00 UNION ALL
SELECT 2, '2012-02-04 00:00:00.000', 15.00 UNION ALL
SELECT 3, '2012-02-07 00:00:00.000 ',190.00 UNION ALL
SELECT 3, '2012-02-07 00:00:00.000', 210.00 UNION ALL
SELECT 3, '2012-02-07 00:00:00.000', 60.00 UNION ALL
SELECT 3, '2012-02-07 00:00:00.000', 40.00
CREATE TABLE #X(ID Int,Dated DATETIME,Amount DECIMAL(10,2))
INSERT INTO #X
SELECT ID,Dated,SUM(Amount) FROM T2000 GROUP BY ID,Dated ORDER BY ID
SELECT ID,Dated,Amount FROM #X
CREATE TABLE Second(ID INT,Whoops VARCHAR(20))
--My mistake ID should have been analysis_code1
INSERT INTO Second
SELECT 1,'YUP' UNION ALL
SELECT 2,'Maybe' UNION ALL
SELECT 3,'Yes' UNION ALL
SELECT 4,'Nope'
SELECT c.ID,c.Dated,c.Amount,s.Whoops
FROM #X c
JOIN Second s ON
c.ID = s.ID
-- Result:
12012-02-03 00:00:00.000250.00YUP
22012-02-04 00:00:00.00065.00Maybe
32012-02-07 00:00:00.000500.00Yes
April 12, 2012 at 3:16 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply