group functions code example

Example: GROUPING Functions

-- It accepts a single column as a parameter and returns "1" 
-- if the column contains a null value generated  as part of a
-- subtotal by a ROLLUP or CUBE operation or "0" for any other value,
-- including stored null values

SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING(fact_1_id) AS f1g, 
       GROUPING(fact_2_id) AS f2g
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
HAVING GROUPING(fact_1_id) = 1 OR GROUPING(fact_2_id) = 1
ORDER BY GROUPING(fact_1_id), GROUPING(fact_2_id);