SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUM

Author

Topic

Marmot74
Starting Member

4 Posts

Posted - 2007-02-16 : 15:25:03

Table Example

TABLE1

ID Total DetailAmount
1 10 5
1 10 5
2 7 3
2 7 2
2 7 2
3 5 5

How would I SUM up TABLE1 on Total Column by Unique ID only? So basically in the Ex. the total would be 22.

I cannot SUM on detail due to the SUM of details not always equaling Total. Total is the amount we want.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 16:05:32

select sum(distinct total) from table1

Peter Larsson
Helsingborg, Sweden

Marmot74
Starting Member

4 Posts

Posted - 2007-02-16 : 16:52:40

That won't work because the Totals are not always Unique. Sorry I did not state that. So basically it could be.

ID Total DetailAmount
1 10 5
1 10 5
2 7 3
2 7 2
2 7 2
3 5 5
4 10 10

etc.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2007-02-16 : 18:56:54

Does this fit your need?

SELECT SUM(a.Total)
FROM (
SELECT ID, Total
FROM MyTable
GROUP BY ID, Total -- DetailAmount, too?
) a

=======================================
If Tyranny and Oppression come to this land, it will be in the guise of fighting a foreign enemy. -James Madison, fourth US president (1751-1836)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-17 : 02:14:22

SELECT SUM(Total)
FROM (SELECT DISTINCT ID, Total, DetailAmount FROM Table1) AS a

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources