COUNT() Function

Question

COUNT() Function

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply

mohedm Valued Member Points: 74 More actions · Answer 1

kapil_kk (4/15/2013)
For the last query:
select COUNT(convert(int,NULL)) from #temp
It will return 0 because when you execute the query:
SELECT CONVERT(int,NULL) it will return NULL and as the return type of COUNT is INT so NULL will implicitly
converted to '0'.
Hope it will clear to you now Yogi 🙂

Converting NULL to integer has nothing to do with the result. COUNT(NULL) will also return 0.

Actually "SELECT 'T' FROM #temp" or "SELECT NULL FROM #temp" both will return 5 rows and 1 column containing that constant ('T' or NULL). This explains why we get 5 when we run "SELECT COUNT(1) from #temp" and we get 1 from "SELECT COUNT(DISTINCT 1) FROM #temp".

But the aggregate functions ignore NULL values. And since we are just counting NULL values, the result will be 0. And that is why we get 0 from "SELECT COUNT(NULL) FROM #temp".

Mohammed

yogyogi97 SSC Veteran Points: 248 More actions · Answer 2

mohedm (4/18/2013) and to ALL
But the aggregate functions ignore NULL values. And since we are just counting NULL values, the result will be 0. And that is why we get 0 from "SELECT COUNT(NULL) FROM #temp".
Mohammed

The COUNT(NULL) expression is not valid and will give error because it cannot be converted to any datatype. that's why I have converted NULL to one of datatype.

And I agree that Aggregate functions ignore NULLs but exception of COUNT() function which count the NULLs as well.

Run and see the result of this query. This query will give 2 count.

select COUNT(*) from (select null col union all select null) a

And check this below two queries and my question is why these both queries giving different count (0 and 2 respectively) whereas both have 2 records.

I am confused here. Please anyone have much clear information on this. I will appreciate that.

select COUNT(convert(int,NULL)) from (select null col union all select null) a

select COUNT(*) from

(

select convert(int,NULL) colNull from (select null col union all select null) a

) b

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 3

Nice question..

Somebody tell me what does that ALL mean in a select statement (4th option)..

--
Dineshbabu
Desire to learn new things..

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 4

Is there anyway to make COUNT() to consider even NULL values?? I mean any set options or something like that?

--
Dineshbabu
Desire to learn new things..

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Dineshbabu (4/18/2013)
Is there anyway to make COUNT() to consider even NULL values?? I mean any set options or something like that?

Yes. Just use COUNT(*).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

srienstr SSCrazy Points: 2435 More actions · Answer 6

Dineshbabu (4/18/2013)
Is there anyway to make COUNT() to consider even NULL values?? I mean any set options or something like that?

To get count() to include null values, you can simply use count(*) or count(constant). Personally, I find it more useful to get count to ignore certain values using count(nullif(ProviderName,'')) or sum(case when SSN not in('','000000000','999999999') then 1 else 0 end)

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 7

Hugo Kornelis (4/18/2013)
Dineshbabu (4/18/2013)
Is there anyway to make COUNT() to consider even NULL values?? I mean any set options or something like that?
Yes. Just use COUNT(*).

Thanks man.. You hit me hard.. I raised the question when i was out of mind..

--
Dineshbabu
Desire to learn new things..

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 8

srienstr (4/18/2013) I find it more useful to get count to ignore certain values using count(nullif(ProviderName,''))

Nice...

--
Dineshbabu
Desire to learn new things..

srienstr SSCrazy Points: 2435 More actions · Answer 9

Dineshbabu (4/18/2013)
Nice question..
Somebody tell me what does that ALL mean in a select statement (4th option)..

I had been nervous about that keyword too. It is apparently the default, and thus only useful for cases where someone reading the code might otherwise assume that you are or had intended to use distinct.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Rose Bud Hall of Fame Points: 3147 More actions · Answer 10

The problem with the original explanation is this
COUNT(convert(int,NULL)) - It will give count as 0 always. Don't know why it is giving 0 count however COUNT() function counts NULL values also.

Personally, I don't mind when the person who posted the question admits there is a gap in their understanding. I know the SSC community will fill it in.

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 11

PRAMANA.DBA

SSCertifiable

Points: 5507

April 19, 2013 at 12:12 am

#1608082

Nice question.....

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 12

Rose Bud (4/18/2013)
Personally, I don't mind when the person who posted the question admits there is a gap in their understanding. I know the SSC community will fill it in.

Totally agreed.

I always check the discussion following the question to not only learn why the things happen the way the answer tells you but to learn many things related to the original question that I wouldn't easily find in any books.

I was gonna ask about the CONVERT(INT, NULL) returned 0 and then I found more than one explanation why that is so even before I could have touched the Reply button. 😀

And the explanation towards the myth about better performance using COUNT(1) today put icing on the cake. Thank you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

siddtharth_anvekar SSC Rookie Points: 25 More actions · Answer 13

select convert(int,NULL) from #temp returns 5 rows with value NULL in each row. So COUNT(convert(int,NULL)) will return 0, since count considers all the rows when we try to consider all the rows in a table eg count(*) or count(1) but if we want to count the rows of table wrt to a particular column “count()” will return count of all non null values in that column.Hence it return 0 in the example

@Cassie SSC Eights! Points: 890 More actions · Answer 14

@Cassie

SSC Eights!

Points: 890

April 22, 2013 at 3:23 pm

#1608878

select convert(int,NULL) will always return NULL

mohedm Valued Member Points: 74 More actions · Answer 15

Thanks you for the correction regarding COUNT(NULL). I found that in T-SQL it is not valid, but I think it works in some other dialects (I think it is valid in the standard SQL).

COUNT(*) returns the number of rows regardless of their contents while COUNT(CAST(NULL AS INT)) returns the count of non-NULL values within a column that contains only NULLs which will always be zero.