MySQL for grouping with one column and getting a subgroup from another column

Question

I have a MySQL Table. I put the schema to this fiddle.
In my table when a user logs in I record him/her to mytable. In one day same user can log in several times. So I need to know how many times a user logged in each day.

I need an output like this:

• 2013-01-30
  -- Michael - 2 times
  -- John - 4 times

• 2013-01-29
  -- Michael - 1 time
  -- John - 1 time
  -- Mary - 1 time
  -- Dean - 1 time

• 2013-01-28
  -- Michael - 3 times
  -- Mary - 1 times

I tried this:

SELECT COUNT(*) AS "times", username, date
FROM mytable GROUP BY date ORDER BY date DESC

But it gave me this:
- 2013-01-30 - Michael - 6 times
- 2013-01-29 - John- 4 times
- 2013-01-28 - Michael - 6 times

Can you recommend a query for this?

Answer 1

Just add username to your GROUP BY clause.

SELECT COUNT(*) AS "times", username, date
FROM mytable 
GROUP BY date, username ORDER BY date DESC

http://sqlfiddle.com/#!2/b5701/11

Answer 2

SELECT date, username, COUNT(1) AS "times" FROM mytable GROUP BY date, username ORDER BY date DESC

Answer 3

you should Group by username also.

try this

   SELECT COUNT(*) AS times, username, date
  FROM mytable group by date,username ORDER BY date DESC

SQL FIDDLE DEMO