NULL Aggregates

Question

Post reply

NULL Aggregates

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
January 6, 2015 at 10:53 pm

#74507

Comments posted to this topic are about the item NULL Aggregates
January 7, 2015 at 3:13 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 44 total)

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Good back to basics question, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Nice question, but I'd argue GROUPING doesn't ignore NULL values as well.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

manik_anu SSCrazy Points: 2377 More actions · Answer 3

wow... +1.. nice question.. thanks...

I had little remembrance of Count.

Using SELECT COUNT(*) or SELECT COUNT(1) (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values.

Using COUNT(<Specific Column Name Here>)will count the number of non-NULL items in the specified column (NULL fields will be ignored).

Manik
You cannot get to the top by sitting on your bottom.

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 4

Not fond of this question.

COUNT(*) counts NULL:s, but COUNT(<expression>) doesn't.

select count(*), count(a)

from (values (1), (NULL)) x(a)

And GROUPING most certainly considers NULL:s.

Just because you're right doesn't mean everybody else is wrong.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 5

Rune Bivrin (1/7/2015)
Not fond of this question.
COUNT(*) counts NULL:s, but COUNT(<expression>) doesn't.
select count(*), count(a)
from (values (1), (NULL)) x(a)
And GROUPING most certainly considers NULL:s.

+1

🙂

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 6

doubt on this question.

Review once again.. :-):-):-)

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 7

I can see where all the doubt in this question arises - as discussed. Only saving grace is the statement in the Aggregate Function link, which states:

Except for COUNT, aggregate functions ignore null values.

.

However even reading the information in the link for COUNT, you get the following:

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.
COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

My guess the problem then is that for all other aggregate functions, it isn't possible to ignore null at all, however with COUNT you have an option based on the execution of the query. Most of the remaining aggregate functions cant be used generically (with *) and so by design have to either include or exclude nulls. The default then is to ignore them.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Michael Riemer (1/7/2015)
I can see where all the doubt in this question arises - as discussed. Only saving grace is the statement in the Aggregate Function link, which states:
Except for COUNT, aggregate functions ignore null values.
.

That statement is incorrect, GROUPING does not ignore null values. It will return a value (0 or 1) even if there is a NULL in the source data.

You can argue though if GROUPING is an aggregate function...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Neil Burton SSC-Insane Points: 22999 More actions · Answer 9

Koen Verbeeck (1/7/2015)
Nice question, but I'd argue GROUPING doesn't ignore NULL values as well.

That's what I took from BOL and got the question wrong.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

vijaygodishala69 Valued Member Points: 69 More actions · Answer 10

Count(*) only count the NULL value but Count(<expression>) doesn't count the NULL value

P. K. Jat SSC Veteran Points: 236 More actions · Answer 11

good question...

but i have some doubt .

i think count(*) and grouping both are not ignoring null values and count(exp) ignoring null values.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

I also clicked COUNT right away, then paused and thought about GROUPING. I actually looked it up and saw that I didn't ignore NULLs. Guessing between the two correct answers, I chose poorly. It's not a big deal, just debatable.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Xavon SSCrazy Points: 2280 More actions · Answer 13

I wonder how MS is defining 'ignore'; because I would argue that if SUM ignored NULLs, I would not have to use half as many ISNULL(<column>, 0) statements.