SQL count(*) and distinct

Question

Why can't we use count(distinct *) in SQL? As in to count all distinct rows?

Answer 1

You can indeed.

If you've got an identifier, though, you won't have any entirely distinct rows. But you could do for instance:

SELECT COUNT(DISTINCT SenderID) FROM Messages

Answer 2

select count(*) from (select distinct * from MyTable) as T

Although I strongly suggest that you re-think any queries that use DISTINCT. In a large percentage of cases, GROUP BY is more appropriate (and faster).

EDIT: Having read the question comments, I should point out that you should never ask the DBMS to do more work than actually needs doing to get a result. If you know in advance that there will not be any duplicated rows in a table, then don't use DISTINCT.

Answer 3

You can select all the columns in your table and group by...

SELECT column1, column2, column3, count(*)
FROM someTable
GROUP BY column1, column2, column3