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

Question

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?

ysth · Answer 1 · 2022-09-14T20:55:36.473

4

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

edited Sep 14 '22 at 20:55

answered Sep 14 '22 at 15:59

ysth

96,171
6
121
214

I am going to test it, never used `with_rollup` before. WIll update, thank you – pileup Sep 14 '22 at 16:15
1

@B.DLiroy updated to exclude the grand total row and designate which grouping each row has (this only works if you do not have null values for these columns, do you?) – ysth Sep 14 '22 at 16:25
Oh there are a few nulls. I might update the table to default to 0's instead of nulls then. What happens in case the `(col_a is not null)` meets a null? I might try to do a case within a case (so in case it's null, make it 0). Also, since I need the results separately. Is there a way to "detect" which rows are for which query? Because although I want a single query (to prevent many DB queries), I still need the results separately – pileup Sep 14 '22 at 18:33
1

Basically it can't tell if the null is because it is a roll-up or is the actual data. What does `select version();` show? You may be able to use a `grouping` function to check what is rolled up instead – ysth Sep 14 '22 at 19:33
I'm sorry, I made a mistake, it's not `mysql`, it's ms sql server. Is the version still matter or it's all bad? Will replacing default values to 0 instead of null be better in this case? – pileup Sep 14 '22 at 20:09
1

I thought sql server didn't support with roll-up at all. I could be wrong though. You could do a union, but that won't be more efficient than just separate queries. – ysth Sep 14 '22 at 20:52
1

I see there is a group by rollup feature, but I don't know how it works – ysth Sep 14 '22 at 20:54

score 1 · Answer 2 · answered Sep 14 '22 at 21:49

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;

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

2 Answers2