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.
41 lines
1.5 KiB
41 lines
1.5 KiB
2 years ago
|
-- Query 4: amount sold for shop_code 351
|
||
|
|
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
calendar_year_lookup.year,
|
||
|
|
calendar_year_lookup.month,
|
||
|
|
SUM(shop_facts.amount_sold) AS "amount sold",
|
||
|
|
SUM(SUM(shop_facts.amount_sold))
|
||
|
|
OVER(ORDER BY calendar_year_lookup.week_key)
|
||
|
|
AS "running amount sold",
|
||
|
|
SUM(SUM(shop_facts.amount_sold))
|
||
|
|
OVER(
|
||
|
|
PARTITION BY calendar_year_lookup.year
|
||
|
|
ORDER BY calendar_year_lookup.week_key
|
||
|
|
)
|
||
|
|
AS "running year amount sold"
|
||
|
|
FROM shop_facts
|
||
|
|
INNER JOIN calendar_year_lookup ON shop_facts.week_key = calendar_year_lookup.week_key
|
||
|
|
WHERE shop_facts.shop_code = 351
|
||
|
|
GROUP BY calendar_year_lookup.year, calendar_year_lookup.month
|
||
|
|
ORDER BY calendar_year_lookup.year, calendar_year_lookup.month
|
||
|
|
) UNION (
|
||
|
|
SELECT
|
||
|
|
NULL AS "year",
|
||
|
|
NULL AS "month",
|
||
|
|
SUM(shop_facts.amount_sold) AS "amount sold",
|
||
|
|
-- Because we are computing the last row by hand (grouping sets being unavailable in MySQL),
|
||
|
|
-- the last row's running amount is defined manually here. I have chosen to set it to the total sum
|
||
|
|
SUM(shop_facts.amount_sold) AS "running amount sold",
|
||
|
|
SUM(shop_facts.amount_sold) AS "running year amount sold"
|
||
|
|
FROM shop_facts
|
||
|
|
WHERE shop_facts.shop_code = 351
|
||
|
|
);
|
||
|
|
|
||
|
|
/* == TEST ==
|
||
|
|
assert(result[12]["running amount sold"] === 807190.1);
|
||
|
|
assert(result[12]["running year amount sold"] === 85616.4);
|
||
|
|
assert(result.length === 37);
|
||
|
|
assert(result[36]["amount sold"] === 3258640.5);
|
||
|
|
*/
|