Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate Totals for ID numbers that have more than one entry Expand / Collapse
Author
Message
Posted Friday, February 10, 2012 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 8:06 AM
Points: 7, Visits: 137
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






Post #1250398
Posted Friday, February 10, 2012 11:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,594, Visits: 24,998
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:
1 2012-02-03 00:00:00.000 250.00
2 2012-02-04 00:00:00.000 65.00
3 2012-02-07 00:00:00.000 500.00


If everything seems to be going well, you have obviously overlooked something.

Ron

Please 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 8:06 AM
Points: 7, Visits: 137
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
Post #1251747
Posted Tuesday, February 14, 2012 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,594, Visits: 24,998
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:
1 2012-02-03 00:00:00.000 250.00 YUP
2 2012-02-04 00:00:00.000 65.00 Maybe
3 2012-02-07 00:00:00.000 500.00 Yes



If everything seems to be going well, you have obviously overlooked something.

Ron

Please 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 8:06 AM
Points: 7, Visits: 137
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse