Q:

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,
       haulier,
       count(*)
FROM demo
WHERE date >= '2016-01-01' AND
      date <= '2016-01-03'
GROUP BY date,
         haulier

Regards

A:

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.

