COUNT of DISTINCT items in a column

Question

Here's the SQL that works (strangely) but still just returns the COUNT of all items, not the COUNT of DISTINCT items in the column.

SELECT DISTINCT(COUNT(columnName)) FROM tableName;

Solution 1

SELECT COUNT(*) FROM tableName

counts all rows in the table,

SELECT COUNT(columnName) FROM tableName

counts all the rows in the table where columnName is not null, and

SELECT (DISTINCT COUNT(columnName)) FROM tableName

counts all the rows in the table where columnName is both not null and distinct (i.e. no two the same)

SELECT DISTINCT(COUNT(columnName)) FROM tableName

Is the second query (returning, say, 42), and the distinct gets applied after the rows are counted.

Solution 2

You need

SELECT COUNT(DISTINCT columnName) AS Cnt
FROM tableName;

The query in your question gets the COUNT (i.e. a result set with one row) then applies Distinct to that single row result which obviously has no effect.

Solution 3

SELECT COUNT(*) FROM (SELECT DISTINCT columnName FROM tableName);