CODEWITHSUNDEEP
mysqlgroup-byrollup
Saurabh Verma
Saurabh Verma

Reputation: 6728

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 |

Upvotes: 0

Views: 2868

Answers (2)

niyoanwxr
niyoanwxr

Reputation: 53

It's unfortunate this question has no correct answer till date. The correct way to implement this is using grouping sets.

Below would give you exactly what you want without the need for a WHERE clause

GROUP BY
    GROUPING SETS((year, country, product), (year, country))

Upvotes: 1

amow
amow

Reputation: 2223

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

having country is not null

Upvotes: 2

Related Questions