You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
17 lines
895 B
17 lines
895 B
2 years ago
|
-- Query 5-2: same as query 5, but with the "grouping" columns
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
calendar_year_lookup.year,
|
||
|
|
outlet_lookup.shop_name,
|
||
|
|
(calendar_year_lookup.year IS NULL) as niveau_annee,
|
||
|
|
SUM(shop_facts.amount_sold) AS total_amount_sold,
|
||
|
|
SUM(shop_facts.quantity_sold) AS total_quantity_sold,
|
||
|
|
(outlet_lookup.shop_name IS NULL) as niveau_magasin,
|
||
|
|
-- Note: GROUPING_ID is only available on Oracle SQL and MSSQL, so we recreate it here
|
||
|
|
(outlet_lookup.shop_name IS NULL) + (calendar_year_lookup.year IS NULL) * 2 as niveau_global
|
||
|
|
FROM outlet_lookup
|
||
|
|
INNER JOIN shop_facts ON shop_facts.shop_code = outlet_lookup.shop_code
|
||
|
|
INNER JOIN calendar_year_lookup ON shop_facts.week_key = calendar_year_lookup.week_key
|
||
|
|
-- Note: shop_name must be used here instead of shop_code for NULL to be handled properly.
|
||
|
|
GROUP BY calendar_year_lookup.year ASC, outlet_lookup.shop_name ASC WITH ROLLUP;
|