Clever Geek Handbook

MySQL to group with one column and get subgroup from another column

I have a MySQL table. I have put the schema in this script .
In my desk, when a user logs in, I write him / her to the desk. After one day, the user can log in multiple times. So I need to know how many times each user has 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 time

I've 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 request for this?

+3


source to share


3 answers


Just add your username to the sentence GROUP BY

.

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


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

+7


source


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



+2


source


you should Group by username

also.

try it

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

SQL FIDDLE DEMO

+1


source






More articles:


All Articles