Rao Rao - 6 months ago 44
SQL Question

How to fill blank while using GROUP BY WITH ROLLUP in MySQL

I have a table with id, desc, quantity. I used GROUP BY WITH ROLLUP to get the subTotal in the result.
Here is SQL Fiddle link

In the result i want to know how to make the "description" column blank for the row created by roll up

In my actual scenario i have other columns also, but rolled up only on one column. So other columns should be empty.

My Result should be like below.

COALESCE(ID,'TOTAL') DESCRIPTION SUM

1 Chocolate Chip Cookies 17
2 Oatmeal Cookies 33
3 Snaker Cookies 49
TOTAL 99

Answer

Here is one method:

SELECT COALESCE(id,'TOTAL'),
       (case when id is null then null else description end) as description,
       sum(qty) AS SUM
FROM item
group by id WITH ROLLUP;