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

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

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

Answer 3

why not?

select 
  count(distinct name)
from 
  people

Answer 4

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 5

UberKludge, and may be postgre specific, but

select count( distinct table::text ) from table

Answer 6

You can try a CTE in Sql Server 2005

;WITH cte AS (
     SELECT DISTINCT Val1,Val2, Val3
     FROM @Table
)
SELECT  COUNT(1)
FROM    cte

To answer the question, From the documentation

Specifies that all rows should be counted to return the total number of rows in a table. COUNT() takes no parameters and cannot be used with DISTINCT. COUNT() does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.

Answer 7

COUNT(*) is the number of rows matching a query.

A row contains unique information such as rowid. All rows are by definition distinct.

You must count the distinct instances of values in some field instead.

Answer 8

some languajes may not be able to handle 'distinct *' so, if you want the distinction made through many columns you might want to use 'distinct ColumnA || ColumnB' , combining the values before judging if they are different. Be mindful whether your variables are numeric and your database handler can make automatic typecast to character strings.