PHP select and group by date (month and year) as well as user

Question

I have a table that has info like this:

id | staff_id | start_date
1  | 14       | 2015-04-02 00:00:00
2  | 16       | 2015-04-15 00:00:00
3  | 14       | 2015-05-31 00:00:00
4  | 12       | 2015-05-04 00:00:00

What I am trying to do is select and count all entries made by each staff member and group them by month and year so it would display like this:

Staff Member  | 12  | 14  | 16
2015-04       | 0   | 1   | 1
2015-05       | 1   | 1   | 0

Can anyone please help me to figure out how to generate this? I just cant figure out how to create a new column for each staff member and also a row for each month/year.

score 1 · Accepted Answer · answered Oct 02 '15 at 15:54

This is a pivot query, but a bit complicated because of the dates:

select date_format(start_date, '%Y-%m') as yyyymm,
       sum(staff_id = 12) as `12`, sum(staff_id = 14) as `14`,
       sum(staff_id = 16) as `16`
from table t
group by yyyymm;

If you don't know the staff ids in advance, then you'll need to use dynamic SQL. Google "MySQL dynamic pivot" to see how to do that.

score 0 · Answer 2 · edited May 23 '17 at 12:23

This is called cross tabulated query (aka pivot table). MySql does not natively support such transformations.

Possible solutions: 1. Generate the counts by user and month in the traditional way and then use php to transform the data into its final resultset. 2. Use a spreadsheet program (eg. Excel) to do the transformation for you again based on the traditional dataset. 3. There are ways to do it in MySql, see link1, link2

The second link is a SO link.

PHP select and group by date (month and year) as well as user

2 Answers2