Mysql selecting unique values

Question

I am really stuck with an sql query... I hope someone can help shed some light for me.

Here is what 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's the catch, I have duplicate data in my table, I would like to pull out all data from this table, non-duplicated based on the email.

I ran a simple count like this:

mysql> select count(*) from france_data;

and this is the result set:

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

Now I tried to run a count like this:

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

Just to see how many unique records I have. Unfortunately this times out.

Does any one know how I can do a count of unique rows and select of the same type?

Answer 1

Please try this

SELECT COUNT(DISTINCT email) FROM france_data