Distinct count of records in a table

Question

Post reply

Distinct count of records in a table

Gimli son of Gloin

Ten Centuries

Points: 1263
More actions
November 12, 2003 at 8:03 am

#84733

How can I get a distinct count of all records in a table without having to list every field in the table. I also only want to return one record with the total distinct count?
I am currently using the following:
SELECT count(*)
FROM tablename
GROUP BY field1, field2, etc...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

linto SSC Eights! Points: 851 More actions · Answer 1

Whats the output of this ?

select count(distinct *) from XXXXX

Linto

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 2

select count(distinct *) from XXXXX

The above gives me: "Incorrect syntax near '*'."

Jonathan SSC-Insane Points: 20427 More actions · Answer 3



SELECT COUNT(*)

FROM

(SELECT DISTINCT *

 FROM tablename) x

Ever heard of a primary key?

--Jonathan

DavidT SSCommitted Points: 1727 More actions · Answer 4

..>>..

you can do

select count(distinct col1), count(distinct col2),... from TableName

But your still going to need to list each field

..<<..

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 5

Gimli son of Gloin

Ten Centuries

Points: 1263

November 12, 2003 at 9:37 am

#481741

Thanks Jonathan! That works great.

paolice SSC Veteran Points: 271 More actions · Answer 6

If you'd like a lightning fast solution, try this:

SELECT count(distinct( BINARY_CHECKSUM(*))) FROM TableName

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 7

SELECT count(distinct( BINARY_CHECKSUM(*))) FROM TableName

The above did run faster at 2:20 (Jonathan's method is 3:03) however, it comes up 145 records shorter. Why would that be?

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

quote:
SELECT count(distinct( BINARY_CHECKSUM(*))) FROM TableName
The above did run faster at 2:20 (Jonathan's method is 3:03) however, it comes up 145 records shorter. Why would that be?

Because rows can differ and still have the same checksum. Checksums are for hashes, not for equivalence testing; all that is guaranteed is that identical values will have the same checksum, not that the same checksum can not be created for different values.

The checksum functions return int values, so there can only be four bytes (2^32) worth of unique checksums. If your rows contain similar values, there is a high probability with large tables that some distinctly different rows will have the same checksums.

--Jonathan