is there any way in mysql to rollup and merge columns at the same time?-CodePudding

if there is a table of the format

continent	country	val
A	X	10
A	Y	20
B	Z	30

the output should look like

region	val
A	30
B	30
X	10
Y	20
Z	30

CodePudding user response：

This is a job for UNION ALL.

SELECT country region, SUM(val) val
  FROM tbl
 GROUP BY country
UNION ALL
SELECT continent region, SUM(val) val
  FROM tbl
 GROUP BY continent

It does two separate aggregating queries, then concantenates their results. UNION ALL is a little cheaper than UNION because UNION removes duplicate rows, which you do not want.

CodePudding user response：

This should do it:

SELECT
    continent AS region,
    SUM( val ) AS val
FROM
    table
GROUP BY
    continent

UNION

SELECT
    country AS region,
    val
FROM
    table