using aggregates on NULL

Rob Philpott

Hi database people, If I have a table with one columm (col) and these rows:

A
A
B
C
C
C
NULL
NULL

and I run the query:

select col, count(col) group by col

I get:

A 2
B 1
C 3
NULL 0

How would I change it so that I got the count of nulls (ie. 2) rather than 0? Any help appreciated! Regards, Rob Philpott.

Michael Potter

Give NULL a value that can be counted.

SELECT
    ISNULL(col,'NULL') AS col, 
    COUNT(col) 
GROUP BY 
    col

albCode

select count(col) from urtable where col=NULL _____________________ Proud to be Albanian _____________________

Rob Philpott

yeah, that would do it. Thanks for the reply but if I want to see the count of null alongside the other results? eg. A 2 B 1 C 3 NULL 2 ? Regards, Rob Philpott.

Steve Schaneville

this will do it: SELECT tt.col, Count(isnull(tt.col, 'NULL')) FROM TempTesting tt GROUP BY tt.col ~Steve www.roundpolygons.com

Chris Meech

I think you need to change

SELECT tt.col, Count(isnull(tt.col, 'NULL'))

to

SELECT isnull(tt.col, 'NULL'), Count(tt.col)

and the group by needs the isnull check also. Chris Meech I am Canadian. [heard in a local bar] When I want privacy, I'll close the bathroom door. [Stan Shannon] BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com] Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me] -- modified at 13:23 Wednesday 22nd March, 2006

Michael Potter

I believe it does put it alongside.

Rob Philpott

Quite right! Genious. Perhaps I should have tried it first... Thanks for your help. :) Regards, Rob Philpott.

Jorge Novo

select sum case when tt.coll is null then 1 else 0 end There are many ways as we can see:^) Hear,See,Learn,Understand,Practice Many can be good,but only ONE can be the best keep practicing