Counting rows with 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

General SQL Server Forums

New to SQL Server Programming

Counting rows with value

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2013-10-11 : 07:41:55

Hallo,

I have a question about T-SQL counting rows with a value:

My query is like this:

SELECT
Name,
A,
B,
C,
D
FROM
Table
ORDER BY
Name

The query result looks like this:

Name A B C D
Bert 2 0 0 0
Bert 3 6 0 1
Mark 0 0 1 0
Mark 0 8 8 0
Mark 5 6 3 0

I want my result to count the cells with a value and group them on name
Like this:

Name A B C D
Bert 2 1 0 1
Mark 3 2 3 0

COUNT() gives back every row from a name.
SUM() adds up the values

I have tried many things with sub queries and combined functions, but up until now with no results.

How do I do this ?
Thanks
Arnold

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-11 : 07:46:06

select name,
sum(case when A>0 then 1 else 0 end) as A,
sum(case when B>0 then 1 else 0 end) as B,
sum(case when C>0 then 1 else 0 end) as C,
sum(case when D>0 then 1 else 0 end) as D
from table
group by name

Madhivanan

Failing to plan is Planning to fail

ArnoldG
Starting Member

36 Posts

Posted - 2013-10-11 : 07:57:43

So simple when you see this...
Thanks a lot. I works perfectly,
Arnold

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 08:19:41

[code]
select name,
count(nullif(A,0)) as A,
count(nullif(B,0)) as B,
count(nullif(C,0)) as C,
count(nullif(D,0)) as D
from table
group by name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-12 : 02:22:17

quote:
Originally posted by visakh16

select name,
count(nullif(A,0)) as A,
count(nullif(B,0)) as B,
count(nullif(C,0)) as C,
count(nullif(D,0)) as D
from table
group by name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I prefer SUM approach http://beyondrelational.com/modules/2/blogs/70/posts/19240/conditional-aggregation-sum-vs-count.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