aggregations_with_rollup.sql

WITH rollup_aggregations AS (
SELECT CASE WHEN GROUPING (level_3_node_id) = 0
               THEN products.level_3_node_id
            WHEN GROUPING (level_2_node_id) = 0
               THEN products.level_2_node_id
            WHEN GROUPING (level_1_node_id) = 0
               THEN products.level_1_node_id
       END AS product_node_id
     -- Aggregates
     , SUM (facts.sales_amount)           AS sum_sales_amount
     , SUM (facts.unit_quantity)          AS sum_unit_quantity
     , COUNT (DISTINCT facts.customer_id) AS distinct_customer_count
     , COUNT (*)                          AS count_of_fact_records
  FROM sales_facts AS facts
    JOIN
       product_reporting_dim AS products
    ON facts.product_id = products.node_natural_key
-- You must add more levels here if you intend to aggregate more than 3 levels...
GROUP BY ROLLUP (products.level_1_node_id
               , products.level_2_node_id
               , products.level_3_node_id
                )
-- Throw out the "GRAND TOTAL" grouping set...
HAVING NOT GROUPING (products.level_1_node_id) = 1
)
-- Now join to the product_reporting_dim to get the sort order sequence...
SELECT LPAD ('-', (product_reporting_dim.level_number - 1) * 7, '-')
     || product_reporting_dim.level_name       AS product_level_name
     , LPAD ('-', (product_reporting_dim.level_number - 1) * 7, '-')
     || product_reporting_dim.node_name        AS product_node_name
     --
     , rollup_aggregations.sum_sales_amount
     , rollup_aggregations.sum_unit_quantity
     , rollup_aggregations.distinct_customer_count
     , rollup_aggregations.count_of_fact_records
  FROM rollup_aggregations
     JOIN
       product_reporting_dim
     ON rollup_aggregations.product_node_id = product_reporting_dim.node_id
ORDER BY product_reporting_dim.node_sort_order ASC
;