ROLL UP to keep only Grand Total and remove sub totals

Question

I have 2 questions for the following query output:


    COALESCE(Division,'Grand Total') Divi,
    COALESCE(Section,'DivTotal') Sec,
    COALESCE(m.Department,'SecTotal') Dept,
    COALESCE(Assortment,'DeptTotal') Assort,
    COALESCE(Promo,'') Prom,
    COALESCE(Range_Details,'') Range_,
    SUM(`WH STK`) WH_STK,
    SUM(`AL STK QTY`) AL_STK,
    SUM(`AL SAL QTY`) AL_SAL,
    SUM(`AL LYa`) LY_a,
    SUM(`AL LYb`) LY_b,
    SUM(`AL MIN`) AL_MIN,
    b.LYa_tot,
    b.LYb_tot,
    COALESCE(ROUND(((SELECT b.LYb_tot)/(SELECT b.LYa_tot))* COALESCE(SUM(`AL SAL QTY`),0),0),0) 'upcoming',
    COALESCE((ROUND(((SELECT b.LYb_tot)/(SELECT b.LYa_tot))*SUM(`AL SAL QTY`),0) +  SUM(`AL MIN`)) - SUM(`AL STK QTY`),0) 'Dispatch'

FROM
(.....I have removed rest of the query part to make it short....) m
GROUP BY
    m.Division,
    m.Section,
    m.Department,
    m.Assortment,
    m.Promo,
    m.Range_Details WITH ROLLUP HAVING
(Division IS NOT NULL AND Section IS NOT NULL AND Department IS NULL AND Assortment IS NULL AND Promo IS NULL AND Range_Details IS NULL )
OR (Division IS NOT NULL AND Section IS NOT NULL and Section NOT IN  ('OTHERS','W_ACCESSORIES','W_BOTTOMWEAR','W_INNERWEAR','W_UPPERWEAR') AND Department IS NOT NULL AND Assortment IS NULL AND Promo IS NULL AND Range_Details IS NULL )
OR (Division IS NOT NULL AND Section NOT IN  ('OTHERS','W_ACCESSORIES','W_BOTTOMWEAR','W_INNERWEAR','W_UPPERWEAR') AND Department IS NOT NULL and Department NOT IN ('GLOVE','RAINCOAT','SWIM SUIT','CORD TROUSERS','COTTON TROUSERS','FORMAL TROUSERS') AND Assortment IS NOT NULL AND Promo IS not NULL AND Range_Details IS not NULL )
OR (Division IS NULL AND Section IS NULL AND Department IS NULL AND Assortment IS NULL AND Promo IS NULL AND Range_Details IS NULL )

enter image description here 1.) How do I keep only 'Grand Total' in last row and remove 'DivTotal','SecTotal', 'DeptTotal'.

2.) Is there any better option to use "WITH ROLLUP" so that I get to chose only columns where I need subtotal instead of having subtotal for all columns in GROUP BY clause?

Note: I am already using multiple UNION, so if possible please give solution without union. cant embed image, apologies for the inconvenience.

Yes, `ROLLUP` can probably be made to work for you here. Kindly add sample data to your question to make the problem more clear (the query alone doesn't help us much). — Tim Biegeleisen, May 13 '21 at 05:11
Just add `AND Sec != 'DivTotal' AND Dept != 'SecTotal' AND Assort != 'DeptTotal')` to your `HAVING` clause? — Nick, May 13 '21 at 05:16
@Nick sorry but that removes the Grand Total row. I need the grand total row with Section, Department and Assortment as blank — Brajesh Sharma, May 13 '21 at 05:58
Ah sorry, yes you'd need to make it `AND (Divi == 'Grand Total' OR Sec != 'DivTotal' AND Dept != 'SecTotal' AND Assort != 'DeptTotal'`)` — Nick, May 13 '21 at 06:35
Didn't work that way either. I have it solved now, solutino posted below. — Brajesh Sharma, May 16 '21 at 18:23

score 0 · Answer 1 · answered May 13 '21 at 12:19

0

I think a CTE and summary is probably a simpler method:

with cte as (
      select m.Division, m.Section, m.Department, m.Assortment,  m.Promo, m.Range_Details,
             . . . 
      from . . .
      group by m.Division, m.Section, m.Department, m.Assortment,  m.Promo, m.Range_Details
     )
select *
from cte
union all
select 'Grand Total', null, null, null, null, null,
       sum(WH_STK), . . . 
from cte;

answered May 13 '21 at 12:19

Gordon Linoff

1,242,037
58
646
786

I have solved it for now but will try this approach later to find it if it makes more sense. Thanks Gordon. – Brajesh Sharma May 16 '21 at 18:23

score 0 · Accepted Answer · answered May 16 '21 at 18:21

I solved it this way, inspired by an answer on stackoverflow itself:

COALESCE(Division,'Grand Total') Divi,
(CASE WHEN Division IS NOT NULL THEN COALESCE(Section,'DivTotal') ELSE '' END) Sec,
(CASE WHEN Section IS NOT NULL THEN COALESCE(m.Department,'SecTotal') ELSE '' END) Dept,
(CASE WHEN m.Department IS NOT NULL THEN COALESCE(Assortment,' Total') ELSE '' END) Assort,
COALESCE(Promo,'') Prom,
(CASE WHEN Division IS NULL  THEN COALESCE(Range_Details,'Grand Total') ELSE COALESCE(Range_Details,'') END) Range_

Thanks everyone for taking time.

ROLL UP to keep only Grand Total and remove sub totals

2 Answers2