HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Query monthly having group_concat and group by need help

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
group-bysqlpivotstackoverflowmysqlgroup-concatenationdba
groupneedqueryhavinghelpmonthlygroup_concatand

Problem

I’m stuck on a join tables query presenting data monthly involving GROUP BY and GROUP_CONCAT.

Here’s a simple client table (DDL and DML at the bottom of this post):

id | Name
1  | Sony
2  | Toshiba
3  | Apple
4  | LG
5  | Uco


Then the event table

id | client_id | date_start
1 | 1 | 2017-01-12 18:44:42
2 | 1 | 2017-01-13 18:44:42
3 | 1 | 2017-01-14 18:44:42
4 | 1 | 2017-02-12 18:44:42
5 | 1 | 2017-03-12 18:44:42
6 | 1 | 2017-07-12 18:44:42
7 | 2 | 2017-02-12 18:44:42
8 | 2 | 2017-03-12 18:44:42
9 | 2 | 2017-04-12 18:44:42
10 | 3 | 2017-01-12 18:44:42
11 | 3 | 2017-01-14 18:44:42
12 | 3 | 2017-01-20 18:44:42
13 | 3 | 2017-03-12 18:44:42
14 | 3 | 2017-05-12 18:44:42
15 | 3 | 2017-06-12 18:44:42
16 | 4 | 2017-07-12 18:44:42
17 | 4 | 2017-07-20 18:44:42
18 | 5 | 2017-09-12 18:44:42
19 | 5 | 2017-10-12 18:44:42
20 | 5 | 2017-03-12 18:44:42


The desired result is as follows. The string number, for example (10-01-12) on Jan/Apple is formatted as id-month-day.

What I’ve done so far is using case when to split result monthly:

```
select * from (
select e.id, c.name as client,
(CASE WHEN MONTH(e.date_start) = 1 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jan,
(CASE WHEN MONTH(e.date_start) = 2 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as feb,
(CASE WHEN MONTH(e.date_start) = 3 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as mar,
(CASE WHEN MONTH(e.date_start) = 4 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as apr,
(CASE WHEN MONTH(e.date_start) = 5 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as may,
(CASE WHEN MONTH(e.date_start) = 6 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start

Solution

Terminology and Methodology

This kind of transformation – rows to columns – is called pivoting. It is typical to pivot data simultaneously with their aggregation, as seems to be a requirement in your case too. In SQL you can do both operations as a single logical step. Other SQL products even offer special syntactical extensions for pivoting, but there is a way to do that using more generic syntax, one that is supported by at least every major RDBMS, which includes MySQL.

The method is called conditional aggregation, and you almost nailed it. The conditional, implemented as a CASE expression in your query, is supposed to go inside the aggregate function, and the criterion that the conditional is checking on (MONTH(e.date_start) in your case) needs to be excluded from the GROUP BY.

So, instead of

SELECT
  CASE WHEN MONTH(e.date_start) = 1 THEN GROUP_CONCAT(...),
  ...
FROM
  ...
GROUP BY
  MONTH(e.date_start),
  client


it should be

SELECT
  GROUP_CONCAT(CASE WHEN MONTH(e.date_start) = 1 THEN ...),
  ...
FROM
  ...
GROUP BY
  MONTH(e.date_start),
  client


The exclusion part may seem counter-intuitive – after all, you are intending to get monthly data. However, you should keep in mind that in SQL you are grouping rows. In your case, one row is one client – therefore, grouping should be by client only. You could say that monthly grouping is implicit, as it is implemented through conditional aggregation only.

Anyway, what about the last row? The last row is special, and not just because it is a rollup row, thus representing aggregated data over the entire set. In my view, it is special more because it contains entirely different data: counts instead of concatenated strings.

Based on that fact, it seems natural to me to consider a distinct logical step – a separate SELECT – for getting the last row's results. The two result sets would then be combined into one with the help of a UNION ALL operator. This approach would make the logic clear, in my opinion: different kinds of data in the output would be accounted for by different legs of the query. And clear logic ultimately means ease of maintenance.

Solution

So, taking into account all of the above, the complete query could look like this:

SELECT
  c.name AS client,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  1 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jan,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  2 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS feb,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  3 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS mar,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  4 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS apr,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  5 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS may,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  6 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jun,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  7 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jul,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  8 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS aug,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  9 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS sep,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 10 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS oct,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 11 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS nov,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 12 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS `dec`
FROM
  event AS e
  INNER JOIN client AS c ON e.client_id = c.id
GROUP BY
  c.name

UNION ALL

SELECT
  NULL,
  COUNT(MONTH(e.date_start) =  1 OR NULL),
  COUNT(MONTH(e.date_start) =  2 OR NULL),
  COUNT(MONTH(e.date_start) =  3 OR NULL),
  COUNT(MONTH(e.date_start) =  4 OR NULL),
  COUNT(MONTH(e.date_start) =  5 OR NULL),
  COUNT(MONTH(e.date_start) =  6 OR NULL),
  COUNT(MONTH(e.date_start) =  7 OR NULL),
  COUNT(MONTH(e.date_start) =  8 OR NULL),
  COUNT(MONTH(e.date_start) =  9 OR NULL),
  COUNT(MONTH(e.date_start) = 10 OR NULL),
  COUNT(MONTH(e.date_start) = 11 OR NULL),
  COUNT(MONTH(e.date_start) = 12 OR NULL)
FROM
  event AS e

;


Or, perhaps, like this, if we wanted to make it look slightly less cumbersome by eliminating repetition of some code:

```
SELECT
client,
GROUP_CONCAT(CASE month WHEN 1 THEN item END SEPARATOR ',') AS jan,
GROUP_CONCAT(CASE month WHEN 2 THEN item END SEPARATOR ',') AS feb,
GROUP_CONCAT(CASE month WHEN 3 THEN item END SEPARATOR ',') AS mar,
GROUP_CONCAT(CASE month WHEN 4 THEN item END SEPARATOR ',') AS apr,
GROUP_CONCAT(CASE month WHEN 5 THEN item END SEPARATOR ',') AS may,
GROUP_CONCAT(CASE month WHEN 6

Code Snippets

SELECT
  CASE WHEN MONTH(e.date_start) = 1 THEN GROUP_CONCAT(...),
  ...
FROM
  ...
GROUP BY
  MONTH(e.date_start),
  client
SELECT
  GROUP_CONCAT(CASE WHEN MONTH(e.date_start) = 1 THEN ...),
  ...
FROM
  ...
GROUP BY
  MONTH(e.date_start),
  client
SELECT
  c.name AS client,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  1 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jan,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  2 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS feb,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  3 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS mar,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  4 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS apr,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  5 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS may,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  6 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jun,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  7 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jul,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  8 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS aug,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN  9 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS sep,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 10 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS oct,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 11 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS nov,
  GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 12 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS `dec`
FROM
  event AS e
  INNER JOIN client AS c ON e.client_id = c.id
GROUP BY
  c.name

UNION ALL

SELECT
  NULL,
  COUNT(MONTH(e.date_start) =  1 OR NULL),
  COUNT(MONTH(e.date_start) =  2 OR NULL),
  COUNT(MONTH(e.date_start) =  3 OR NULL),
  COUNT(MONTH(e.date_start) =  4 OR NULL),
  COUNT(MONTH(e.date_start) =  5 OR NULL),
  COUNT(MONTH(e.date_start) =  6 OR NULL),
  COUNT(MONTH(e.date_start) =  7 OR NULL),
  COUNT(MONTH(e.date_start) =  8 OR NULL),
  COUNT(MONTH(e.date_start) =  9 OR NULL),
  COUNT(MONTH(e.date_start) = 10 OR NULL),
  COUNT(MONTH(e.date_start) = 11 OR NULL),
  COUNT(MONTH(e.date_start) = 12 OR NULL)
FROM
  event AS e

;
SELECT
  client,
  GROUP_CONCAT(CASE month WHEN  1 THEN item END SEPARATOR ',') AS jan,
  GROUP_CONCAT(CASE month WHEN  2 THEN item END SEPARATOR ',') AS feb,
  GROUP_CONCAT(CASE month WHEN  3 THEN item END SEPARATOR ',') AS mar,
  GROUP_CONCAT(CASE month WHEN  4 THEN item END SEPARATOR ',') AS apr,
  GROUP_CONCAT(CASE month WHEN  5 THEN item END SEPARATOR ',') AS may,
  GROUP_CONCAT(CASE month WHEN  6 THEN item END SEPARATOR ',') AS jun,
  GROUP_CONCAT(CASE month WHEN  7 THEN item END SEPARATOR ',') AS jul,
  GROUP_CONCAT(CASE month WHEN  8 THEN item END SEPARATOR ',') AS aug,
  GROUP_CONCAT(CASE month WHEN  9 THEN item END SEPARATOR ',') AS sep,
  GROUP_CONCAT(CASE month WHEN 10 THEN item END SEPARATOR ',') AS oct,
  GROUP_CONCAT(CASE month WHEN 11 THEN item END SEPARATOR ',') AS nov,
  GROUP_CONCAT(CASE month WHEN 12 THEN item END SEPARATOR ',') AS `dec`
FROM
  (
    SELECT
      c.name AS client,
      MONTH(e.date_start) AS month,
      CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item
    FROM
      event AS e
      INNER JOIN client AS c ON e.client_id = c.id
  ) AS derived
GROUP BY
  client

UNION ALL

SELECT
  NULL,
  COUNT(month =  1 OR NULL),
  COUNT(month =  2 OR NULL),
  COUNT(month =  3 OR NULL),
  COUNT(month =  4 OR NULL),
  COUNT(month =  5 OR NULL),
  COUNT(month =  6 OR NULL),
  COUNT(month =  7 OR NULL),
  COUNT(month =  8 OR NULL),
  COUNT(month =  9 OR NULL),
  COUNT(month = 10 OR NULL),
  COUNT(month = 11 OR NULL),
  COUNT(month = 12 OR NULL)
FROM
  (
    SELECT
      MONTH(e.date_start) AS month
    FROM
      event AS e
  ) AS derived

;
SELECT
  client,
  IF(client IS NULL, COUNT(month =  1 OR NULL), GROUP_CONCAT(CASE month WHEN  1 THEN item END SEPARATOR ',')) AS jan,
  IF(client IS NULL, COUNT(month =  2 OR NULL), GROUP_CONCAT(CASE month WHEN  2 THEN item END SEPARATOR ',')) AS feb,
  IF(client IS NULL, COUNT(month =  3 OR NULL), GROUP_CONCAT(CASE month WHEN  3 THEN item END SEPARATOR ',')) AS mar,
  IF(client IS NULL, COUNT(month =  4 OR NULL), GROUP_CONCAT(CASE month WHEN  4 THEN item END SEPARATOR ',')) AS apr,
  IF(client IS NULL, COUNT(month =  5 OR NULL), GROUP_CONCAT(CASE month WHEN  5 THEN item END SEPARATOR ',')) AS may,
  IF(client IS NULL, COUNT(month =  6 OR NULL), GROUP_CONCAT(CASE month WHEN  6 THEN item END SEPARATOR ',')) AS jun,
  IF(client IS NULL, COUNT(month =  7 OR NULL), GROUP_CONCAT(CASE month WHEN  7 THEN item END SEPARATOR ',')) AS jul,
  IF(client IS NULL, COUNT(month =  8 OR NULL), GROUP_CONCAT(CASE month WHEN  8 THEN item END SEPARATOR ',')) AS aug,
  IF(client IS NULL, COUNT(month =  9 OR NULL), GROUP_CONCAT(CASE month WHEN  9 THEN item END SEPARATOR ',')) AS sep,
  IF(client IS NULL, COUNT(month = 10 OR NULL), GROUP_CONCAT(CASE month WHEN 10 THEN item END SEPARATOR ',')) AS oct,
  IF(client IS NULL, COUNT(month = 11 OR NULL), GROUP_CONCAT(CASE month WHEN 11 THEN item END SEPARATOR ',')) AS nov,
  IF(client IS NULL, COUNT(month = 12 OR NULL), GROUP_CONCAT(CASE month WHEN 12 THEN item END SEPARATOR ',')) AS `dec`
FROM
  (
    SELECT
      c.name AS client,
      MONTH(e.date_start) AS month,
      CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item
    FROM
      event AS e
      INNER JOIN client AS c ON e.client_id = c.id
  ) AS derived
GROUP BY
  client
  WITH ROLLUP
;

Context

StackExchange Database Administrators Q#183632, answer score: 10

Revisions (0)

No revisions yet.