Count the number of not null columns using a case statement

Question

I need some help with my query...I am trying to get a count of names in each house, all the col#'s are names.

Query:

SELECT House#,
    COUNT(CASE WHEN col#1 IS NOT NULL THEN 1 ELSE 0 END) + 
    COUNT(CASE WHEN col#2 IS NOT NULL THEN 1 ELSE 0 END) +
    COUNT(CASE WHEN col#3 IS NOT NULL THEN 1 ELSE 0 END) as count
  FROM myDB
 WHERE House# in (house#1,house#2,house#3)
 GROUP BY House#

Desired results:

house 1 - the count is 3 / house 2 - the count is 2 / house 3 - the count is 1

...with my current query the results for count would be just 3's

If you are able to change the database schema, I suggest you do that instead. Comma-separated values in a column is not a great idea. You ideally would have a `HouseName` table that just has one house/name combination per row. Then your query becomes a simple count/group. — Blorgbeard, Aug 16 '17 at 19:48
Do you have the possibility of firstname,lastname or any other commas? — Jacob H, Aug 16 '17 at 19:48
sorry I was a bit (very) vague... the commas just separate the names. If I was to write it in a question/answer paragraph, it would be... peter, paul and mary live in house 1. sarah and sally live in house 2. joe lives in house 3. how may people live in house 1 and house 2 and house 3....answer, respectfully is 3,2,1 — Benjo, Aug 16 '17 at 19:57
I am pretty sure I need to use a counter (loop?) but can't find anything on stackoverflow or online that address this... — Benjo, Aug 16 '17 at 19:59
This is a very **very** different problem than the one posted in the question — Lamak, Aug 16 '17 at 20:01
yes, I realize that....sorry about that! just when I thought I was doing well in advancing with sql, I then find myself still at the 2nd grade level — Benjo, Aug 16 '17 at 20:03
Possible duplicate of [How to count instances of character in SQL Column](https://stackoverflow.com/questions/1860457/how-to-count-instances-of-character-in-sql-column) — Javlon Ismatov, Aug 16 '17 at 20:19
I am not sure about the protocol because the question I asked was not very clear...please advise.In case I can leave it be, here is the query I would use and the results are the same as I indicated above;SELECT houseNo, COUNT(CASE WHEN name1 IS NULL THEN 0 ELSE 1 END) + COUNT(CASE WHEN name2 IS NULL THEN 0 ELSE 1 END) + COUNT(CASE WHEN name3 IS NULL THEN 0 ELSE 1 END) + COUNT(CASE WHEN name4 IS NULL THEN 0 ELSE 1 END) FROM mydatabase GROUP BY houseNo — Benjo, Aug 17 '17 at 23:20
Just FYI, `COUNT(CASE WHEN name1 IS NULL THEN 0 ELSE 1 END)` should not return anything different than `COUNT(name1)` as `NULL` values are not counted by the `COUNT()` aggregate function. — David Faber, Mar 20 '18 at 16:04

score 6 · Answer 1 · answered Aug 16 '17 at 19:48

6

In this case, it seems that counting names is the same as counting the commas (,) plus one:

SELECT House_Name, 
       LEN(Names) - LEN(REPLACE(Names,',','')) + 1 as Names
FROM dbo.YourTable;

answered Aug 16 '17 at 19:48

Lamak

69,480
12
108
116

@scsimon yeah, sorry, I normally wait for clarification, but for some reason I thought in this was case was pretty clear...my bad – Lamak Aug 16 '17 at 19:51

score 2 · Answer 2 · answered Aug 16 '17 at 19:53

2

Another option since Lamak stole my thunder, would be to split it and normalize your data, and then aggregate. This uses a common split function but you could use anything, including STRING_SPLIT for SQL Server 2016+ or your own...

declare @table table (house varchar(16), names varchar(256))
insert into @table 
values
('house 1','peter, paul, mary'),
('house 2','sarah, sally'),
('house 3','joe')

select
    t.house
    ,NumberOfNames = count(s.Item)
from
    @table t
    cross apply dbo.DelimitedSplit8K(names,',') s
group by
    t.house

answered Aug 16 '17 at 19:53

S3S

24,809
5
26
45

Good one. Though reading the last comment by the op, we are all very wrong – Lamak Aug 16 '17 at 20:01
HAHA, we were way of judging by that comment @Lamak – S3S Aug 16 '17 at 20:06
1

yeah, I don't think that something like that can be done with 100% precision in sql – Lamak Aug 16 '17 at 20:10

score 1 · Answer 3 · answered Aug 16 '17 at 20:03

Notice how the answers you are getting are quite complex for what they're doing? That's because relational databases are not designed to store data that way.

On the other hand, if you change your data structure to something like this:

house   name
1       peter
1       paul
1       mary
2       sarah
2       sally
3       joe

The query now is:

select house, count(name)
from housenames
group by house

So my recommendation is to do that: use a design that's more suitable for SQL Server to work with, and your queries become simpler and more efficient.

score 0 · Answer 4 · answered Aug 16 '17 at 19:51

0

One dirty trick is to replace commas with empty strings and compare the lengths:

SELECT house + 
       ' has ' + 
       CAST((LEN(names) - LEN(REPLACE(names, ',', '')) + 1) AS VARCHAR) +
       ' names'
FROM   mytable

answered Aug 16 '17 at 19:51

Mureinik

297,002
52
306
350

score 0 · Answer 5 · answered Aug 16 '17 at 19:58

You can parse using xml and find count as below:

Select *, a.xm.value('count(/x)','int') from (
    Select *, xm = CAST('<x>' + REPLACE((SELECT REPLACE(names,', ','$$$SSText$$$') AS [*] FOR XML PATH('')),'$$$SSText$$$','</x><x>')+ '</x>' AS XML)   from #housedata
) a

score 0 · Answer 6 · answered Aug 16 '17 at 20:18

0

select House, 'has '+cast((LEN(Names)-LEN(REPLACE(Names, ',', ''))+1) as varchar)+' names' 
from TempTable

answered Aug 16 '17 at 20:18

Javlon Ismatov

194
12

Count the number of not null columns using a case statement

6 Answers6