MySQL count elements in a database based on how many unique values there are in a field.

Question

(Sorry for the title, I don't really know how to phrase that :-) )

I have a table that has a date and a uid fields.

I need to get the number of uid for each date, currently I'm doing it in php running multiple queries like this one:

SELECT COUNT(uid) FROM users where Date = 'xxx';

Is there a simple way to achieve this with only an sql query?

Answer 1

Use the group by clause:

SELECT Date, COUNT(uid) FROM users group by Date;

Answer 2

SELECT Date, COUNT(uid) FROM users GROUP BY Date

Answer 3

To count the number of unique values use DISTINCT:

SELECT COUNT(DISTINCT uid) AS cnt
FROM users
GROUP BY `Date`

If your column is a datetime then you should use the DATE function to get the date part only:

SELECT COUNT(DISTINCT uid) AS cnt
FROM users
GROUP BY DATE(`Date`)