Select query - 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 query

Author

Topic

wecoyo
Starting Member

2 Posts

Posted - 2007-06-11 : 17:29:01

I have a problem I hope someone can help with summarizing and grouping data. Any help pointing myself in the right direction would be appreciated.

Desired results:

Fld1 total xxx_count yyy_count
=======================================
aaaa 5 3 2
bbbb 2 2 0
cccc 3 0 3
etc...

table:
=============

fld1 fld2
================
bbbb xxx
aaaa xxx
aaaa xxx
cccc yyy
aaaa xxx
aaaa yyy
bbbb xxx
cccc yyy
aaaa yyy
cccc yyy

TIA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 20:03:09

[code]
select fld1,
total = count(*),
xxx_count = count(case when fld2 = 'xxx' then 1 end),
yyy_count = count(case when fld2 = 'yyy' then 1 end)
from table
group by fld1
[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-11 : 22:25:44

quote:

Originally posted by khtan


select fld1, 
       total     = count(*),
       xxx_count = count(case when fld2 = 'xxx' then 1 end),
       yyy_count = count(case when fld2 = 'yyy' then 1 end)
from   table
group by fld1

Is it that simple? I started writing something like that then realized what if there were more than 2 types?
The OP could probably explain more. If thats the case, he/she needs to use PIVOT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 22:28:20

I always assume the simplest case first.

wecoyo
Starting Member

2 Posts

Posted - 2007-06-12 : 08:54:16

That was just what I needed! Thank you very much. I think I was trying to overcomplicate it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:16:49

Other approach

select fld1, 
       total     = count(*),
       xxx_count = sum(case when fld2 = 'xxx' then 1 else 0 end),
       yyy_count = sum(case when fld2 = 'yyy' then 1 else 0 end)
from   table
group by fld1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:18:26

quote:
Originally posted by dinakar

quote:
Originally posted by khtan

select fld1,
total = count(*),
xxx_count = count(case when fld2 = 'xxx' then 1 end),
yyy_count = count(case when fld2 = 'yyy' then 1 end)
from table
group by fld1

KH

Is it that simple? I started writing something like that then realized what if there were more than 2 types?
The OP could probably explain more. If thats the case, he/she needs to use PIVOT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Then dynamic Crosstab

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources