distinct count(*)

Question

How to do get distinct count(*) in MySQL.

for example, in table1 i have 10 million record, there are duplicate records in it.

I want to find out distinct count(*) from the table.

I know, I can do

select distinct * from table1 but, i don't want to fetch 10 million records, not even want to insert distinct records in other table like, create table table2 select distinct * from table1

So, please help me with any other option.

Help from anyone welcome

Answer 1

Maybe like:

SELECT SUM(cnt) FROM ( SELECT COUNT(*) as cnt FROM tab GROUP BY some_value )

Answer 2

SELECT COUNT(DISTINCT field) FROM table

or

SELECT COUNT(*) FROM table GROUP BY field;

(btw - this has been answered quite a few times elsewhere on this site)

Answer 3

Try using a subquery:

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM table1) T1