问题
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.
回答1:
use
SELECT count( DISTINCT(email) ) FROM orders
Distinct provide unique email ids and then simply count them.
回答2:
SELECT count(DISTINCT(email)) FROM orders
its different from your posting, since its filters out the duplicates before counting it
回答3:
For best performance you should use:
SELECT
sub.email,
count(1) as 'count_unique'
FROM
(SELECT email FROM orders GROUP by email) sub
回答4:
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.
来源:https://stackoverflow.com/questions/16697215/mysql-count-number-of-unique-values