CONDITIONAL COUNT - 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

Transact-SQL (2000)

CONDITIONAL COUNT

Author

Topic

glorijaa
Starting Member

4 Posts

Posted - 2005-06-13 : 04:33:46

Hi everyone,
I have a table tracking transactions made by customers.
I want to make a report counting number of transactions made by each customer (count (*)..group by customerid), but since there are 2 types of transactions I want the result to return 2 columns containing separate counts (column Result1 counting transactions where type='x', and another column Result2 for type='y')
Example:

CUSTOMERID | Result1 | Result2
A23B | 5 | 7
B74M | 74 | 91

I would really appreciate if anyone could help. Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-13 : 04:41:00

[code]
Select CustomerId, (Select count(*) from yourTable where CustomerId=T.CustomerId and type='x') Result1,(Select count(*) from yourTable where CustomerId=T.CustomerId and type='y')
from yourTable T group by CustomerId
[/code]

Madhivanan

Failing to plan is Planning to fail

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-06-13 : 04:41:06

[code]
select customerid,
sum(case when type = 'x' then 1 else 0 end) as typex,
sum(case when type = 'y' then 1 else 0 end) as typey
from t
group by customerid
[/code]

glorijaa
Starting Member

4 Posts

Posted - 2005-06-13 : 06:02:14

Thanks a lot to both of you.
In the meantime (based on LarsG's answer) I figured out that this also works:

select customerid,
COUNT (case when type = 'x' then 'x' else NULL end) as 'x trn',
COUNT (case when type = 'y' then 'y' else NULL end) as 'y trn'
from t
group by customerid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources