CODEWITHSUNDEEP
sqlsql-server
Benjo
Benjo

Reputation: 95

Count the number of not null columns using a case statement

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

Upvotes: 3

Views: 1057

Answers (6)

Javlon Ismatov
Javlon Ismatov

Reputation: 194

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

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

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.

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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

Upvotes: 0

S3S
S3S

Reputation: 25122

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

Upvotes: 2

Mureinik
Mureinik

Reputation: 311583

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

Upvotes: 0

Lamak
Lamak

Reputation: 70658

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;

Upvotes: 6

Related Questions