"Adding" GROUP BY's and ORDER BY's in one query instead of separate-CodePudding

I have a base query that has a few variations, all of them are different from each other in the GROUP BY and ORDER BY clauses.

These are the variations:

SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c"
FROM my_table
GROUP BY col_a

SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c"
FROM my_table
GROUP BY col_a, col_b
ORDER BY col_a

SELECT SUM(col_a) AS "col_a", SUM(col_b), AS "col_b", SUM(col_c) as "col_c"
FROM my_table
GROUP BY col_a, col_b, col_c
ORDER BY col_a, col_b

Is it possible to do it in same query? Or I should get the base data and sum it server side/client side?

CodePudding user response：

In MySQL (which was the original question) you can do it in the same query using WITH ROLLUP:

select sum(col_a) as col_a, sum(col_b) as col_b, sum(col_c) as col_c, count(*),
    case (col_a is not null)   (col_b is not null)   (col_c is not null) when 3 then 'col_a,col_b,col_c' when 2 then 'col_a,col_b' when 1 then 'col_a' end grouped_by
from my_table
group by col_a, col_b, col_c with rollup
having grouped_by is not null
order by grouped_by,col_a, col_b, col_c

fiddle

CodePudding user response：

Yes, you can use GROUPING SETS or ROLLUP for this. The former is more flexible, if a little more verbose.

SELECT
  SUM(col_a) AS col_a,
  SUM(col_b) AS col_b,
  SUM(col_c) as col_c
FROM my_table
GROUP BY GROUPING SETS (
    (col_a),
    (col_a, col_b),
    (col_a, col_b, col_c)
)
ORDER BY col_a, col_b, col_c;

To identify the rolled up rows, you can do something like this

SELECT
  CASE WHEN GROUPING(col_b) = 0 AND GROUPING(col_c) = 0
         THEN 'By A, B, C'
       WHEN GROUPING(col_b) = 0
         THEN 'By A, B'
       ELSE   'By A'
       END AS GroupingType,
  SUM(col_a) AS col_a,
  SUM(col_b) AS col_b,
  SUM(col_c) as col_c
FROM my_table
GROUP BY GROUPING SETS (
    (col_a),
    (col_a, col_b),
    (col_a, col_b, col_c)
)
ORDER BY col_a, col_b, col_c;