Стоимость остатков по категориям по месяцам
SQL, по которому посчитано
WITH monthly AS (
SELECT
date_trunc('month', date) AS month,
category,
sum(value) AS stock_value
FROM stock
WHERE date >= (
SELECT date_trunc('month', max(date)) - INTERVAL '11 months'
FROM stock
)
GROUP BY 1, 2
)
SELECT
strftime(month, '%Y-%m') AS month,
round(sum(CASE WHEN category = 'Диваны' THEN stock_value ELSE 0 END), 0) AS divany,
round(sum(CASE WHEN category = 'Кровати' THEN stock_value ELSE 0 END), 0) AS krovati,
round(sum(CASE WHEN category = 'Кухни' THEN stock_value ELSE 0 END), 0) AS kuhni,
round(sum(CASE WHEN category = 'Шкафы' THEN stock_value ELSE 0 END), 0) AS shkafy,
round(sum(CASE WHEN category = 'Освещение' THEN stock_value ELSE 0 END), 0) AS osveshchenie
FROM monthly
GROUP BY month
ORDER BY month