October 2, 2007 at 4:41 am
do we have a select statment which can count only non null values against each column and report the total sum. I have about 19 columns in my table and about 85000 rows.
I need total sum of non null values in my table.
October 2, 2007 at 4:52 am
If you want a single result then something like this would do:
select sum (
case when is not null then 1 else 0 end
+ case when is not null then 1 else 0 end
...
)
from
If you want a count by column then:
select sum ( case when is not null then 1 else 0 end ) as Col1_Count
, sum( case when is not null then 1 else 0 end ) as Col2_Count
...
from
Jez
October 2, 2007 at 4:54 am
Would you mind to open Book on Line and read about aggregate system functions?
COUNT, SUM, etc.
_____________
Code for TallyGenerator
October 2, 2007 at 4:54 am
this would give you the sample structure to test each column in a single pass...is that what you wanted, or did you want where an entire row was null?
select SUM(CASE WHEN COL1 IS NULL THEN 0 ELSE 1 END) AS COL1,
SUM(CASE WHEN COL2 IS NULL THEN 0 ELSE 1 END) AS COL2
SUM(CASE WHEN COL3 IS NULL THEN 0 ELSE 1 END) AS COL3
FROM SOMETABLE
Lowell
October 2, 2007 at 4:55 am
Jez, same advice to you.
_____________
Code for TallyGenerator
October 2, 2007 at 5:12 am
maybe this will get you going ..
SELECT CASE t1.[ORDINAL_POSITION] WHEN 1 THEN 'SELECT ' ELSE ' , ' end
+ ' sum( CASE WHEN ' + t1.[COLUMN_NAME] + ' is null then 0 else 1 end ) as Count_' + t1.[COLUMN_NAME]
+ CASE t1.[ORDINAL_POSITION] WHEN t2.[MAX_ORDINAL_POSITION] THEN ' from [' + t1.[TABLE_SCHEMA] + '].[' + t1.[TABLE_NAME] + '] ;' ELSE ' ' end
FROM [INFORMATION_SCHEMA].[COLUMNS] t1
INNER JOIN
( SELECT [TABLE_NAME], [TABLE_SCHEMA], MAX([ORDINAL_POSITION]) AS MAX_ORDINAL_POSITION
FROM [INFORMATION_SCHEMA].[COLUMNS]
GROUP BY [TABLE_NAME], [TABLE_SCHEMA]
)t2
ON t1.[TABLE_NAME] = t2.[TABLE_NAME]
AND t1.[TABLE_SCHEMA] = t2.[TABLE_SCHEMA]
--WHERE t1.[TABLE_NAME] = 'mytable'
ORDER BY t1.[TABLE_NAME], t1.[TABLE_SCHEMA], t1.[ORDINAL_POSITION]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 2, 2007 at 5:12 am
SELECT COUNT(Col1),
COUNT(Col2),
COUNT(Col3),
COUNT(*)
FROM Table1
N 56°04'39.16"
E 12°55'05.25"
October 2, 2007 at 5:21 am
Thanks all,
Now another issue,
I have a column named 'Temp'. If used in query, SQL takes it as a system name, and highlights it blue.
Any workaround??
Its not possible for me to change the column name.
October 2, 2007 at 5:25 am
just put brackets around the columnname ( look at my previous reply 😉 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply