Hugo Kornelis (8/24/2010)
da-zero (8/24/2010)
I would've liked a paragraph on the behaviour of NULL in COUNT(*) and COUNT(DISTINCT *).There's not much to say about that, as COUNT(*) does not care if values are NULL or not (it simply counts rows), and COUNT(DISTINCT *) is a syntax error.
In COUNT(columnname), NULL is relevant. If you demand a COUNT(Age) from the patients table, you are not requesting the number of patients in the database, but the number of ages in the database. Since NULL represents missing data, a row with a NULL age does not represent an age in the database. These rows are correctly excluded from the COUNT() result.
You're right, I didn't think about the syntax 🙂
What I meant was that the author should've included the COUNT function in the article, since it behaves differently when you specify additional arguments.
E.g. I would've liked examples of COUNT(*), COUNT(expression), COUNT(DISTINCT expression) and COUNT(ALL expression).
That would've made the article more comprehensive.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP