DISTINCT then 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 2000 Forums

SQL Server Development (2000)

DISTINCT then SUM

Author

Topic

Three Rings
Starting Member

1 Post

Posted - 2006-03-22 : 16:06:56

Dear All,

I have some raw data that look like this:

TYPE QUANTITY
---- --------
A 1
B 3
C 5
A 1
B 3
C 5
A 1
B 3
C 5

What I need is an SQL statement that will return the single value 9.

That is, an SQL statement that DISTINCTs the values down to:

TYPE QUANTITY
---- --------
A 1
B 3
C 5

and then SUMs them:

-
9

Can it be done? Any help greatly appreciated.

I am using SQL*Server... I haven't checked the version but I would prefer a solution which doesn't rely on proprietary functions - if possible.

Many thanks in advance.

Best regards,
Three Rings

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 16:15:11

is this what you're looking for?


select	sum(quantity)
from	(select distinct type, quantity from <myTable>) a

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-23 : 01:32:49

or

Select sum(distinct quantity) from yourTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources