Excluding NULL values from COUNT Function

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

General SQL Server Forums

New to SQL Server Programming

Excluding NULL values from COUNT Function

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-07-03 : 10:21:22

Hi Memebers: I have the following table.

ORDER ADDRESS || COL2

123 MAIN 2
456 KING 1
987 OTTAWA NULL

How can I set up a COUNT function so that COUNT (COL2) function does not count the NULL values? I need it to return a value of 2, not 3.

Any suggestions? THANKS!

SELECT [ORDER ADDRESS], COUNT (COL2)
FROM DATA T1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-03 : 10:33:12

The way you have written the query, the parser will complain, because you don't have a group by clause. So your choices are:


SELECT [ORDER ADDRESS], COUNT (COL2) 
FROM DATA T1 GROUP BY [ORDER ADDRESS] -- will give, 1,1,0

SELECT  COUNT (COL2) 
FROM DATA T1 -- will give 2

or, if you are on SQL 2005 or higher


SELECT [ORDER ADDRESS], COUNT (COL2) OVER () 
FROM DATA T1 -- will give 2

In any case, count function does not count nulls.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-03 : 11:38:16

Count(Col2) will return 2 if that's all the data in the table, as count when passed a column name counts the non-null values in the column only. Count(*) will return 3.

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-03 : 16:31:27

Are you sure your NULL data really is null value? I think your NULL data really is a string of "NULL" (4 characters).

Try this

SELECT [ORDER ADDRESS], COUNT (NULLIF(COL2, 'NULL'))
FROM DATA T1
GROUP BY [ORDER ADDRESS]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources