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.
Subscribe to:
Post Comments (Atom)
Good info Friedrich ..
ReplyDeleteAlso congrats as u winner again of T-SQL challenge ..
Keep Rocking...
Thanks Ashish.
ReplyDeleteI have explained my solution for the TSQL challenge 5 from www.beyondrelation.com. Please have a look at it.
Very useful info fred...
ReplyDelete