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.