Reputation: 10974

SQL and distinct

I'm having trouble on generating a report in MySQL. I have this table:

CREATE TABLE users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    birthday DATE,
    title VARCHAR(255)
);

I need to generate a report that throws how many titles exist for a given month. My first idea was something like this:

SELECT COUNT(DISTINCT(MONTH(birthday))) AS q 
  FROM users

Of course that only returns how many months exist in the records. What I need is something like this:

Month | Count
-------------
2     | 384
5     | 131
12    | 585

Upvotes: 1

Answers (4)

Reputation: 424983

Even simpler:

SELECT MONTH(birthday), COUNT(*)
FROM user
GROUP BY 1

Upvotes: 2

Reputation: 78447

Like this?

SELECT MONTH(birthday), COUNT(*)
FROM user
GROUP BY MONTH(birthday)

Upvotes: 2

Reputation: 16757

This should work:

SELECT MONTH(birthday), COUNT(*) AS BirthdayCount
FROM user
GROUP BY MONTH(birthday)

This will give you the month number and the count of the records in that month, thus what you are looking for.

Upvotes: 2

Reputation: 4950

You need a GROUP BY month(birthday) in there.

select month(birthday), count(month(birthday)) 
from users 
group by month(birthday);

Upvotes: 1