Monday, April 27, 2009

Careful when you use count() function

This was a silly mistake that I committed when writing a procedure.It took me great deal of time to figure out the reason. I thought I should share this.

create table #example (id_no int) --create a temporary table
insert into #example select 1
insert into #example select 2
insert into #example select 2
insert into #example select 4
insert into #example select null

when I execute a query like
select COUNT(id_no) from #example --output is 4

but when i execute the query like
select COUNT(*) from #example --output is 5

The reason I found was that if any row has a null value and if that columns is passed as parameter for count function then null values for that specific columns will be skipped. Only not null values will be taken into consideration.

using below query will result in taking only distinct not null values into consideration.
select COUNT(distinct id_no) from #example --output is 3

drop table #example

I would be glad to add more information on this topic if you give your valuable comments.

3 comments:

  1. Good info Friedrich ..
    Also congrats as u winner again of T-SQL challenge ..
    Keep Rocking...

    ReplyDelete
  2. Thanks Ashish.

    I have explained my solution for the TSQL challenge 5 from www.beyondrelation.com. Please have a look at it.

    ReplyDelete