8394221 with rollup

0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

Stack Overflow

Q&A for programmers

-- 8394221 with rollup
-- Enter Query Description

-- Sample for 8394221

WITH DATA AS
( SELECT 'A' as Cat, 1 as subcat, 123 as amount
  UNION SELECT 'A', 2, 456
  UNION SELECT 'B', 1, 222
  UNION SELECT 'B', 2, 333
  UNION SELECT 'B', 3, 444
) , CONFIG as
( SELECT 'B' as cat , 1 as subcat) 
, g as ( 
SELECT 
   
        d.cat, 
        d.subcat,
        c.subcat config_subcat, 
        sum(amount) amount,
        GROUPING(c.subcat) subcatgroup
FROM   data d 
LEFT JOIN config c
ON d.cat = c.cat 
    
GROUP BY
   d.cat, 
   d.subcat,
   c.subcat with rollup
)

SELECT
g.cat, 
g.subcat,
case when g.config_subcat is null then g.amount 
     WHEN g.subcat = g.config_subcat THEN g2.amount 
     ELSE 0 end amount
FROM g 

LEFT JOIN g g2
ON g.cat = g2.cat and g2.subcatgroup= 1
and g.subcat is not null and g2.subcat is null

WHERE g.subcatgroup= 0

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached