Beyerz Beyerz - 2 months ago 6
MySQL Question

Mysql selecting unique values

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

Please try this

SELECT COUNT(DISTINCT email) FROM france_data