0

I understand that we can use ROLLUP to get the total sum in group by query. However, is it possible to have more than 1 group by columns in group by query, but ROLLUP can be applied to ONLY ONE column ?

eg, default ROLLUP behaviour:

SELECT year, country, product, SUM(profit) 
FROM sales
GROUP BY year, country, product WITH ROLLUP

| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |

Required Output (applying RollUp on 3rd column, but group by on 3rd as well as 2nd column) :

| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
Saurabh Verma
  • 6,328
  • 12
  • 52
  • 84

1 Answers1

2

You can use having to limit the output.
In your case, just put

having country is not null

amow
  • 2,203
  • 11
  • 19
  • This does limit the output, but you still burn computation time on the rollups you didn't want to do. I ended up here because of rollups on several columns that produced very large intermediary sets and can't just filter after the fact due to performance implications (and `having` doesn't kick in until after rollups have occurred). – bsplosion Oct 15 '21 at 01:09