SQL count(*) and distinct

Question

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

Answer 1

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 2

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 3

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.

Answer 4

why not?

select 
  count(distinct name)
from 
  people

Answer 5

UberKludge, and may be postgre specific, but

select count( distinct table::text ) from table

Answer 6

select count (Tag_no) from tab_raw_tag_value where tag_no in (select distinct tag_no from tab_raw_tag_value)