MySQL Rollup format

Question

How do I format the "total" row of a rollup?

Backgroup I have a MySQL select statement that is using group by with rollup it works however, for formatting reasons I need to identify what row is a "detail" and what row is a "total." Doing this by a simple RowType column that is a 1 or a zero. I figured this would work:

select
   if (MyId is null, 1,0) as RowType,
   MyId,        
   sum(Quantity) as Quantity
from MyTable
group by MyId with rollup

This does not work. However, If I create a view of that select statement then select that view and do this it does:

Create view MyView as 
select
    MyId,   
    Sum(Quantity) as Quantity     
from MyTable
group by MyId with rollup;

select
    if (MyId is null, 1,0) as RowType,
    MyId,
    Quantity
from MyView;

Is there a better way? I am going to have to do this for a fair amount of queries and maintaining two sets is good way to have errors.

edit: screwed up my second select code and fixed it

The first query should work. Please explain what "This does not work" means exactly. Sample data, current result and expected result would help to understand. — Paul Spiegel, Nov 01 '22 at 16:35

score 1 · Accepted Answer · answered Nov 01 '22 at 15:54

You can put the grouped query into a subquery.

This is needed because grouping doesn't happen after selecting, so the SELECT list can't refer to the value created by WITH ROLLUP in the same query.

select
    if(MyId is null, 1,0) as RowType, MyId, Quantity
FROM (
    SELECT MyId, sum(Quantity) as Quantity
    from MyTable
    group by MyId with rollup
) AS x

MySQL Rollup format

1 Answers1