June 14, 2010 at 6:44 am
How to count number of NULLS in a row? or the other way how to count the not null values in the row ?
June 14, 2010 at 6:49 am
It all depends on your SET_ANSI_NULLS setting..
but for starters i give u this:
-- NULL COUNT
SELECT COUNT(*) FROM TABLE
WHERE COLUMN IS NULL
-- NOT NULL COUNT
SELECT COUNT(*) FROM TABLE
WHERE COLUMN IS NOT NULL
HTH
June 14, 2010 at 7:13 am
I think I am not clear in my question
coulmn1 column2 cloumn3 column4
1 1 NULL NULL 2
2 NULL 2 3 5
In the table above i am trying to get the number of Columns with NULL values for each row
June 15, 2010 at 4:47 pm
As ColdCoffee said, your SET_ANSI_NULLS setting will matter. This is one way to get your counts:
SET ANSI_NULLS ON
go
declare @t_temp table
(
ID int,
Column1 int,
Column2 int,
Column3 int,
Column4 int,
Column5 int
)
insert into @t_temp(ID, Column1, Column2, Column3, Column4, Column5)
select 1, 1, null, null, 4, 2 union
select 2, null, null, 5, null, 1 union
select 3, 1, 9, 5, 8, null union
select 4, null, 2, 7, null, null union
select 5, 4, null, 7, null, 6
select ID,
NonNullCount = COUNT(Column1) + COUNT(Column2) + COUNT(Column3) + COUNT(Column4) + COUNT(Column5),
NullCount = 5 - (COUNT(Column1) + COUNT(Column2) + COUNT(Column3) + COUNT(Column4) + COUNT(Column5))
from @t_temp
group by ID
Both the null count and the non-null counts are listed, obviously you would use the one you needed.
June 16, 2010 at 4:12 am
This variation is independent of the ANSI_NULLS setting. I use the same table variable @t_temp kindly supplied by bteraberry
;WITH cte AS
(
SELECT T.ID, Z.Col FROM @t_temp AS T
CROSS APPLY
(
VALUES
(Column1),
(Column2),
(Column3),
(Column4),
(Column5)
) AS Z (Col)
)
SELECT ID, COUNT(col) as NonNullCount, COUNT(*) - COUNT(col) as NullCount
FROM cte
GROUP BY ID
June 16, 2010 at 4:47 am
Steve ,I like the use of the values clause 😎
June 16, 2010 at 5:12 am
Thanks Dave, yes it does make things a lot tidier
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply