Explain the behavior of DISTINCT COUNT (*)

Question

Answering another question, he fulfilled a request that gave me an unexpected result. It would be normal to combine COUNT and DISTINCT as COUNT (field DISTINCT) to get the number of different nonzero values ​​in the field.

I also tried the DISTINCT COUNT (field), expecting to show me the “number of samples”, which will basically always be 1. But that’s 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 like a simple query mechanism ignores DISTINCT when using this method. I tested this against SQL Server, MySQL, Oracle, PostGreSQL and SQLite, and it is the same thing.

Here's the SQL Server script you're interested in.

, ANSI - ? , , .

Answer 1

first row :

SELECT COUNT(DISTINCT id) FROM Bob

, COUNT OF DISTINCT IDs 0,0,1,null, COUNT OF 0,1,null, null , 2

Second row:

SELECT COUNT(id) FROM Bob  

COUNT OF IDs 0,0,1,null, , , null 3

third row:

SELECT DISTINCT COUNT(id) FROM Bob

DISTINCT COUNT OF IDs, COUNT OF IDs, Group by, , COUNTs, COUNT OF IDs 3, SQL Fiddle .

fourth row:

SELECT COUNT(*) FROM Bob

COUNT OF ROWS, 4.

fifth row:

SELECT DISTINCT COUNT(*) FROM Bob

DISTINCT COUNT OF the ROWS, Group by , , 4 .

Answer 2

, , , .

, , - .

Select Distinct Count. , , , 4 , . , Compute Scalar, 4, , , SQL 4.

Select Count ( ). , 4 . SQL , , 2 , 2.