Count number of unique values
MysqlMysql Problem Overview
If I have three columns:
orderNumber, name, email
and I would like to count how many unique emails are in the table how would I go about doing so?
A statement like:
SELECT count(email) FROM orders
gives me the total count.
I tried SELECT DISTINCT count(email) FROM orders
but that does not seem to be giving me the numbers I am expecting.
Mysql Solutions
Solution 1 - Mysql
use
SELECT count( DISTINCT(email) ) FROM orders
Distinct provide unique email ids and then simply count them.
Solution 2 - Mysql
SELECT count(DISTINCT(email)) FROM orders
its different from your posting, since its filters out the duplicates before counting it
Solution 3 - Mysql
The accepted soultion doesn't work for me - it returns a "1" for each unique email address in the table.
This is what I had to do to get the info I needed:
select email, count(email) AS total from sysAccessLog group by email order by total desc
Which returns a list of email addresses and the number of occurrences.
Solution 4 - Mysql
For best performance you should use:
SELECT
sub.email,
count(1) as 'count_unique'
FROM
(SELECT email FROM orders GROUP by email) sub