SQL count(*) and distinct

Why can't we use count(distinct *) in 开发者_如何学JAVASQL? As in to count all distinct rows?

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.

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

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

why not?

select 
  count(distinct name)
from 
  people

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

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.

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.

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.

UberKludge, and may be postgre specific, but

select count( distinct table::text ) from table

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