How to Get NotNull value Coloumn Count? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Get NotNull value Coloumn Count?

Author

Topic

ssmani84
Starting Member

20 Posts

Posted - 2010-05-06 : 03:16:39

hi friends ,

i want to get Count of NotNull value Coloumns

my table structure is below

ColoumnName col1 col2 col3 col4 col5 col6 col7 col8 col9 col10
Value P P P P null P P null null null

here i want to get Notnull value count Notmull=6 and null=4
how to get this? pls help me

ssmaniyadav

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-06 : 04:08:12

SELECT 10 - NullsCount AS NotNullCount, NullsCount
FROM (SELECT CASE WHEN col1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN col2 IS NULL THEN 1 ELSE 0 END +
...
CASE WHEN col10 IS NULL THEN 1 ELSE 0 END AS NullsCount
FROM YourTable) AS T

ssmani84
Starting Member

20 Posts

Posted - 2010-05-06 : 07:13:45

Thanks malpashaa ..its working fine......

ssmaniyadav

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-06 : 07:42:25

I thought it was this you were after but reading it again I can see that the previous answer was what you were looking for. Posting it anyway:

SELECT COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableName'
	AND IS_NULLABLE = 'YES'

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources