Count based on column value - 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)

Count based on column value

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-05-05 : 03:20:00

i have data like this...

CREATE TABLE Try1(tblname varchar(10),Createdby varchar(10))

insert into Try1
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1','user1' UNION ALL
select 'Temp1',NULL UNION ALL
select 'Temp1',NULL

I want results like this
tblname cnt agncnt
Temp1 9 12

is there any other way to achieve this...
i used it like this..

select
tblname,
SUM(case when createdby IS null then 1 else 0 end) as cnt,
SUM(case when createdby is not null then 1 else 0 end) as agncnt
from Try1 group by tblname

is there any simple way...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-05-05 : 03:24:38

select
tblname,
SUM(case when createdby IS null then 1 else 0 end) as cnt,
count(createdby) as agncnt
from Try1 group by tblname

Karthik
http://karthik4identity.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 03:26:34

What is wrong with this solution?
It looks simple enough.

No, you're never too old to Yak'n'Roll if you're too young to die.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-05-05 : 03:34:24

quote:
Originally posted by webfred

What is wrong with this solution?
It looks simple enough.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks fred for your reply...
Am just hunting for another way, i want to remove that case clause from this query and this is just for learning purpose, trying to thinking out of box.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-05-05 : 03:39:20

quote:
Originally posted by ashishashish

quote:
Originally posted by webfred

What is wrong with this solution?
It looks simple enough.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks fred for your reply...
Am just hunting for another way, i want to remove that case clause from this query and this is just for learning purpose, trying to thinking out of box.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

U tried this one..

select
tblname,
SUM(case when createdby IS null then 1 else 0 end) as cnt,
count(createdby) as agncnt
from Try1 group by tblname

Karthik
http://karthik4identity.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 09:48:49

If you want to completely avoid CASE expression

select
tblname,
count(*)-count(Createdby) as cnt ,
count(Createdby) as agncnt
from Try1
group by tblname

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-07 : 09:54:33

quote:
Originally posted by madhivanan

If you want to completely avoid CASE expression

select
tblname,
count(*)-count(Createdby) as cnt ,
count(Createdby) as agncnt
from Try1
group by tblname

Madhivanan

Failing to plan is Planning to fail

I always enjoy to read your easy solutions which I have not seen because I am thinking too complicated.
madhi, you're cool

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 10:41:35

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

If you want to completely avoid CASE expression

select
tblname,
count(*)-count(Createdby) as cnt ,
count(Createdby) as agncnt
from Try1
group by tblname

Madhivanan

Failing to plan is Planning to fail

I always enjoy to read your easy solutions which I have not seen because I am thinking too complicated.
madhi, you're cool

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources