0

Result of the query is presented in multiple rows for the same cluster type, but I would like to have it in one row but multiple columns.

SELECT cluster,pop_group,count(pop_group) as total 
FROM gcro_final 
WHERE cluster in('A','D','E','H','M') 
GROUP BY cluster,pop_group 
ORDER BY cluster,pop_group;

Actual result of the query is like this:

+---------+-----------+-------+
| cluster | pop_group | total |
+---------+-----------+-------+
| A       |         1 |   153 |
| A       |         2 |     1 |
| D       |         1 |   258 |
| D       |         2 |     1 |
| E       |         1 |   204 |
| H       |         1 |    49 |
| M       |         1 |    13 |
+---------+-----------+-------+

Is it possible to have output like this:

+---------------------+
| A | 1 | 153 | 2 | 1 |
| D | 1 | 258 | 2 | 1 |
| E | 1 | 204 |       |
| H | 1 | 49          |
| M | 1 | 13          |
+---------------------+
GMB
  • 216,147
  • 25
  • 84
  • 135
Dusan
  • 1
  • 1
    Is there a defined limit of pop groups or is it n (could be 1-100+) You could pivot the data based on popgroup but you wouldn't get the pop group as part of the row data, instead it woudl be the column header. – xQbert Apr 04 '19 at 21:02
  • Why? Presentation/formatting is a function of the UI layer not the database. I suppose we could group_Concat(popGroup,Count(popgroup Seperator '|')... and only use cluster in teh group by – xQbert Apr 04 '19 at 21:08
  • This question seams to go in the direction off a [MySQL dynamic pivot table](https://stackoverflow.com/questions/17773045/mysql-dynamic-pivot-table) but iam not sure.. Topicstarter can you add column headers to the expected output to make it more clear, [this](https://www.tablesgenerator.com/text_tables) tool can help you making a better table to paste here. – Raymond Nijland Apr 04 '19 at 21:16
  • Thanks a lot, but I don't know, in advance, list of pop_group. In this example list is short (1 and 2) but other parameters have much more elements. – Dusan Apr 05 '19 at 06:26

2 Answers2

0

If you known in advance the list of pop_groups that may exist, you can use conditional aggregation.

Here is an example for pop_groups 1 and 2, as shown in your sample data:

SELECT 
    cluster,
    MAX(CASE WHEN pop_group = 1 THEN pop_group END) pop_group1,
    SUM(CASE WHEN pop_group = 1 THEN 1 ELSE 0 END) pop_total1,
    MAX(CASE WHEN pop_group = 2 THEN pop_group END) pop_group2,
    SUM(CASE WHEN pop_group = 2 THEN 1 ELSE 0 END) pop_total2
FROM gcro_final 
WHERE cluster in('A','D','E','H','M') 
GROUP BY cluster 
ORDER BY cluster;

You extend the logic to add more columns.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks a lot, but I don't know, in advance, list of pop_group. In this example list is short (1 and 2) but other parameters have much more elements. – Dusan Apr 05 '19 at 06:26
  • @Dusan: a SQL query must return a *fixed* set of columns. One solution would be to add as many SUM() expressions as the maximum pop_groups that might exist. Else, you would need to use *dynamic SQL*, but I believe this is beyond the scope of your question... – GMB Apr 05 '19 at 07:33
0

Maybe using group_Concat?

DEMO

note: all you really need is to wrap your query in

SELECT cluster, group_Concat(concat(pop_group,'|',total) order by pop_group SEPARATOR '|') as Combined FROM (YOUR QUERY) GROUP BY cluster

SELECT cluster, group_Concat(concat(pop_group,'|',total) order by pop_group SEPARATOR '|') as Combined FROM (
SELECT 'A' `cluster`,         1 `pop_group` ,   153 `total` UNION ALL 
SELECT 'A',                2 ,     1 UNION ALL 
SELECT  'D',                1 ,   258 UNION ALL 
SELECT  'D',                2 ,     1 UNION ALL 
SELECT  'E',                1 ,   204 UNION ALL 
SELECT  'H',                1 ,    49 UNION ALL 
SELECT  'M',                1 ,    13) Z
GROUP BY CLUSTER

    cluster Combined
1   A   1|153|2|1
2   D   1|258|2|1
3   E   1|204
4   H   1|49
5   M   1|13
xQbert
  • 34,733
  • 2
  • 41
  • 62