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.
| 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. |
|
|
|
|
|
|
Counting multiple fields in a table