Select DISTINCT with 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)

Select DISTINCT with SUM?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-08 : 12:52:49

I've been experimenting for a couple hours with no luck, so I thought I'd ship this question over to the experts. I'm no SQL guru by any stretch of the imagination, so here we go:

In the sample table below I have (3) data types. I would like to have a single query where I can get the following results:

X = 3
Y = 3
Z = 2

Sample Data:


====================
|  ID   |   Type   |
====================
|   1   |    X     |
|   2   |    X     |
|   3   |    X     |
|   4   |    Y     |
|   5   |    Y     |
|   6   |    Y     |
|   7   |    Z     |
|   8   |    Z     |

Is this possible, or will I have to write multiple queries? Thanks,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 12:57:33

[code]SELECT SUM(CASE WHEN Type='X' THEN 1 ELSE 0 END) AS [X],
SUM(CASE WHEN Type='Y' THEN 1 ELSE 0 END) AS [Y],
SUM(CASE WHEN Type='Z' THEN 1 ELSE 0 END) AS [Z]
FROM Table
[/code]

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-08 : 13:07:35

Thanks very much, that works well.

However, is it possible to get this information even if I am not aware in advance of the "types". For example, if a new type of "W" is introduced, I would like to be able to display this data without changing the query.

--------------------
Actually, now that I think about it, this may work well as a 2-query scenario. One query to get the distinct types, then another to get the SUMS. Does that make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 13:12:35

yup. see below
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-08 : 13:55:49

Wow...that's a little complicated for my experience level. I tried to run the samples against Adventureworks (don't have Northwind installed) but I couldn't find valid data to correlate. Maybe I'll just skip my ideas for this and just create a new table where I can increment the sums as data gets inserted. Someday when I have a better grasp on this SQL stuff I'll come back to this.

Thanks,
Matt

srouse
Starting Member

7 Posts

Posted - 2010-02-08 : 14:36:40

I may not fully understand the question, but can you just do a count and a group by?

select Type, count(*)
from TABLE
group by Type

Scott

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 08:41:13

quote:
Originally posted by srouse

I may not fully understand the question, but can you just do a count and a group by?

select Type, count(*)
from TABLE
group by Type

Scott

Yes. In this case you are correct

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 08:46:34

quote:
Originally posted by srouse

I may not fully understand the question, but can you just do a count and a group by?

select Type, count(*)
from TABLE
group by Type

Scott

thats enough unless you want to get totals for each category as columns like

X Y Z
3 3 2

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources