0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

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) 

SELECT
   t.CAT, 
   t.SUBCAT, 
   SUM(t.AMOUNT) AMOUNT
FROM
(
SELECT d.cat, 
       d.subcat, 
       CASE 
         WHEN c.subcat IS NULL 
               OR c.subcat = d.subcat THEN d.amount 
         ELSE 0 
       END AS amount 
FROM   data d 
       LEFT JOIN config c 
         ON d.cat = c.cat 
         
UNION ALL 

SELECT d.cat, 
       d.subcat, 
       d2.amount 
FROM   data d 
       INNER JOIN config c 
         ON ( d.cat = c.cat ) 
       INNER JOIN data d2 
         ON c.cat = d2.cat 
            AND c.subcat <> d2.subcat 
            AND c.subcat = d.subcat 
) t
GROUP BY
    cat,
    subcat
ORDER BY
    cat,
    subcat

fork query

created dec 6 11 conradfrix