Jacob Jensen Jacob Jensen - 6 months ago 18
SQL Question

MySQL - Group and summarize time entries on projects

I've got these timerecording data from toggl.com where I need to have my reports formatted a bit different than what they supply - that's why I thought I'd try to import the csv data to MySQL and group and format it myself via MySQL and PHP etc. but I'm stuck almost before I started - got the data into MySQL, so now I need to output it in a html table... somehow.

I've got these data:

+-----------+---------------------------------------+------------+----------+
| Project | Task name | Date | Duration |
+-----------+---------------------------------------+------------+----------+
| Project 1 | Task name 2 | 2016-05-12 | 00:22:03 |
| Project 2 | Task name 1 | 2016-05-12 | 00:04:24 |
| Project 2 | Task name 1 | 2016-05-12 | 00:06:27 |
| Project 1 | 3. correction | 2016-05-13 | 00:08:00 |
| Project 1 | Small correction | 2016-05-13 | 00:02:02 |
| Project 1 | Last correction | 2016-05-16 | 00:05:36 |
| Project 1 | Created low-res | 2016-05-16 | 00:04:21 |
| Project 2 | Layout | 2016-05-24 | 00:27:45 |
| Project 2 | Layout | 2016-05-25 | 01:00:00 |
| Project 2 | Finishing | 2016-05-25 | 00:15:48 |
+-----------+---------------------------------------+------------+----------+


What I dream to achieve is data for each project that looks like this:

Project 2
-----------------------------------------------------
Date Tasks Total
-----------------------------------------------------
12/05/16 Task name 1 00:10:51
24/05/16 Layout 00:27:45
25/05/16 Layout, Finishing 01:15:48
-----------------------------------------------------
Project 2 total 01:54:24


So for each Project, each Date should have its own row, including Date, Task names (comma delimited) and the sum of the Duration for that Date

And then a project total.

Any help would be very appreciated!! :-)

EDIT:

SELECT
Project,
`Task name`,
Date,
Duration,
SEC_TO_TIME( SUM( TIME_TO_SEC( `Duration` ) ) ) AS TotalDuration
FROM timerecordings
GROUP BY Project, Date, `Task name` WITH ROLLUP


Gives me (don't look at the date and time formating, it's excel that does it):

+-----------+------------------+------------+----------+----------+
| Project 1 | Task name 2 | 12/05/2016 | 00.22.03 | 00.22.03 |
| Project 1 | NULL | 12/05/2016 | 00.22.03 | 00.22.03 |
| Project 1 | 3. correction | 13/05/2016 | 00.08.00 | 00.08.00 |
| Project 1 | Small correction | 13/05/2016 | 00.02.02 | 00.02.02 |
| Project 1 | NULL | 13/05/2016 | 00.02.02 | 00.10.02 |
| Project 1 | Created low-res | 16/05/2016 | 00.04.21 | 00.04.21 |
| Project 1 | Last correction | 16/05/2016 | 00.05.36 | 00.05.36 |
| Project 1 | NULL | 16/05/2016 | 00.05.36 | 00.09.57 |
| Project 1 | NULL | NULL | 00.05.36 | 00.42.02 |
| Project 2 | Task name 1 | 12/05/2016 | 00.04.24 | 00.10.51 |
| Project 2 | NULL | 12/05/2016 | 00.04.24 | 00.10.51 |
| Project 2 | Layout | 24/05/2016 | 00.27.45 | 00.27.45 |
| Project 2 | NULL | 24/05/2016 | 00.27.45 | 00.27.45 |
| Project 2 | Finishing | 25/05/2016 | 00.15.48 | 00.15.48 |
| Project 2 | Layout | 25/05/2016 | 01.00.00 | 01.00.00 |
| Project 2 | NULL | 25/05/2016 | 01.00.00 | 01.15.48 |
| Project 2 | NULL | NULL | 01.00.00 | 01.54.24 |
| NULL | NULL | NULL | 01.00.00 | 02.36.26 |
+-----------+------------------+------------+----------+----------+

Answer

You can use GROUP_CONCAT aggregate function to combine all task names in one string. Use SEPARATOR keyword to define the separator (default: ','). Use DISTINCT if you don't want same task to be listed multiple times.

For one project:

SELECT Date,
  GROUP_CONCAT(DISTINCT `Task name` SEPARATOR ', ') AS Task,
  SEC_TO_TIME(SUM(TIME_TO_SEC(Duration))) AS Total
FROM timerecordings r
WHERE Project = 'Project 2'
GROUP BY Date WITH ROLLUP;

For all projects:

SELECT SELECT Project, Date, 
  GROUP_CONCAT(DISTINCT `Task name` SEPARATOR ', ') AS Task,
  SEC_TO_TIME(SUM(TIME_TO_SEC(Duration))) AS Total
FROM timerecordings r
GROUP BY Project, Date WITH ROLLUP;

sqlfiddle

Comments