pepper candy pepper candy - 1 year ago 54
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download