Counting multiple fields in a table

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)

Counting multiple fields in a table

Author

Topic

barnsley
Starting Member

34 Posts

Posted - 2011-07-19 : 06:25:32

I am trying to count more than 1 table column in SQL.
(e.g. counting the number of null entries in the entire field)

For counting 1 column I have the following code:

Select COUNT(1) From tbl1 WHERE NULLIF(Surname, '') IS NULL

But i'm not sure how to do it for more than 1 field in the same table (e.g. counting all null entries in field called forename)?

mark.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-19 : 06:56:37

[code]
select
count(case when nullif(Surname,'') is null then 1 end) as NullSurnames,
count(case when nullif(Forename,'') is null then 1 end) as NulForenames
from
tbl1[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-19 : 07:19:49

I would use SUM with ELSE 0 to avoid unneccesary warning on NULL


select
   sum(case when nullif(Surname,'') is null then 1 else 0 end) as NullSurnames,
   sum(case when nullif(Forename,'') is null then 1 else 0 end) as NulForenames
from
   tbl1

Madhivanan

Failing to plan is Planning to fail

barnsley
Starting Member

34 Posts

Posted - 2011-07-19 : 07:47:40

Both work!
thanks a lot.

mark.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources