pepper candy pepper candy - 1 year ago 48
MySQL Question

Multiple count columns on a single statement grouped by distinct username

Fairly new to SQL and having some troubles with a particular statement.

Here is some sample data:

date | username
2016-01-01 | JIM
2016-01-01 | BOB
2016-01-01 | BOB
2016-01-02 | JIM
2016-01-03 | JIM
2016-01-03 | JIM
2016-01-03 | BOB

What I am trying to do is count each distinct date per username except I want each date on its own column with an individual count per date per username.

I will be supplying each individual date via PHP and was hoping you could return the following table in a single SQL Query:

username | 2016-01-01 | 2016-01-02 | 2016-01-03
JIM | 1 | 1 | 2
BOB | 2 | 0 | 1

So far all I can achieve is a list of dates, usernames and the count per day per username:

SELECT date,
FROM demo
WHERE date >= '2016-01-01' AND
date <= '2016-01-03'
GROUP BY date,


Answer Source

You can use conditional aggregation:

select s.username,
       sum(date = '2016-01-01') as cnt_20060101,
       sum(date = '2016-01-02') as cnt_20060102,
       sum(date = '2016-01-03') as cnt_20060103
from sample s
group by s.username;

If you don't know the specific dates, then you need to use dynamic SQL. You can Google "MySQL dynamic pivot" to see examples of how to do that.