Explain the Behavior of DISTINCT COUNT(*)
sql
Solution
In the `first row` i mean:
SELECT COUNT(DISTINCT id) FROM Bob
it means that `COUNT OF DISTINCT IDs` in this sample we got `0,0,1,null` it returns COUNT OF `0,1,null` but `null` can't count there for it returns `2`
in the `Second row`:
SELECT COUNT(id) FROM Bob
it means `COUNT OF IDs` in this sample it `0,0,1,null` but as i said `null` can't count and it returns `3`
in the `third row`:
SELECT DISTINCT COUNT(id) FROM Bob
it means `DISTINCT COUNT OF IDs` but here it means `COUNT OF IDs`,if we use `Group by` and the count have got many results then it returns distinct of `COUNTs`,and here it returns `COUNT OF IDs` i mean `3`,this SQL Fiddle can give you better clues about it.
in the `fourth row`:
SELECT COUNT(*) FROM Bob
it means `COUNT OF ROWS` and here we got `4` rows.
in the `fifth row`:
SELECT DISTINCT COUNT(*) FROM Bob
it means `DISTINCT COUNT OF the ROWS` but with out the `Group by` it returns one value and there for that value is distinct too, it returns `4` here.
Problem
In answering another question a ran a query that gave me an unexpected result. It would be normal to combine COUNT and DISTINCT as COUNT(DISTINCT field) to get the number of non-null distinct values in field. I also tried DISTINCT COUNT(field) expecting that to show me the "number of counts" which would be basically always be 1. But that is not what it does. ``` CREATE TABLE Bob (id INT) INSERT INTO Bob VALUES (0),(0),(1),(NULL) SELECT COUNT(DISTINCT id) FROM Bob --Result: 2 SELECT COUNT(id) FROM Bob --Result: 3 SELECT DISTINCT COUNT(id) FROM Bob --Result: 3 SELECT COUNT(*) FROM Bob --Result: 4 SELECT DISTINCT COUNT(*) FROM Bob --Result: 4 ``` Instead it looks as if the query engine simple ignores DISTINCT when used this way. I tested this against SQL Server, MySQL, Oracle, PostGreSQL, and SQLite and the behavior is the same. Here's the SQL Server fiddle is you are curious. Can you explain the behavior based on the ANSI standard or some other historical convention? Or maybe my original expected behavior is simple flawed in some way.