Why select count(distinct *) does not work?

1

Hello SQL Team!

Any idea why select count(distinct *) does not work?

Thanks!

2

Can you please define "does not work" ?

1 Like
3

"(DISTINCT *)" has no meaning in SQL Server. COUNT ( * ) counts the presence of rows. To apply DISTINCT to the row, it must be outside parentheses.

1 Like
4

Mmm, I want to count the distinct records of a table.
When I type:

select count(distinct *)
from table

It says:

Incorrect syntax near '*'.

5 
WITH distinctRows
AS (
SELECT DISTINCT * FROM table;
)
SELECT COUNT(*) FROM distinctRows;

Or - use a derived table:

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM table t)

Note: if you have a primary key defined - then all rows in the table are DISTINCT. If you don't have a primary key defined - why not?

1 Like
6

Yes, because as I stated before, "(DISTINCT *)" has no valid meaning in SQL Server. It doesn't exist.

1 Like