Search code examples
mysqlgroup-byrollup

ROLLUP on only 1 column in mysql

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 |
Solution

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

    having country is not null