Mysql choosing unique values

Question

I am really stuck in sql query ... Hope someone can help me shed some light.

Here is my table looks like

mysql> show fields from france_data; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | email | varchar(45) | YES | | NULL | | | name | varchar(45) | YES | | NULL | | | lastname | varchar(45) | YES | | NULL | | | quality | varchar(45) | YES | | NULL | | | country | varchar(45) | YES | | NULL | | | state | varchar(45) | YES | | NULL | | | year | varchar(45) | YES | | NULL | | | owner | varchar(45) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) 

Here is the catch, I have duplicate data in my table, I would like to pull all the data from this table that is not duplicated based on email.

I missed a simple account:

 mysql> select count(*) from france_data; 

and this is the result:

 +----------+ | count(*) | +----------+ | 2405259 | +----------+ 1 row in set (0.01 sec) 

Now I tried to start the count as follows:

 mysql> select count(*) from france_data group by email; 

Just to find out how many unique entries I have. Sorry, this is the time.

Does anyone know how I can count the number of unique rows and select the same type?

Answer 1

Please, try

 SELECT COUNT(DISTINCT email) FROM france_data 

Answer 2

If you are looking for unique email-based strings, just

 select count(distinct email) from france_data 

gotta do the trick.

If you also want to view each duplicate email, try the following:

 select email, count(*) as cnt from france_data group by email order by cnt desc; 

Answer 3

Please, try

 select count(distinct email) from france_data order by email;