Optimize subquery in SELECT

Question

My table schema is as follow:

Indexes:

• products.id PRIMARY KEY
• products.description UNIQUE
• expenses.id PRIMARY KEY
• expenses.product_id FOREIGN KEY to product.id

---

My goal is to load

1. Cost of each product of current month (AS costs_november)
2. Cost of each product of last month (AS costs_october)
3. Change in costs of current month compared to last
   (current month costs - last month costs) (AS costs)
4. Percentage change of current month costs compared to last
   (last month costs * 100 / current month costs) (AS percent_diff)

---

I've managed to code SQL that does exactly that:

SELECT description, (SUM(cost) - IFNULL(
(
    SELECT SUM(cost)
    FROM expenses
    WHERE month = 9 AND year = 2019 AND product_id = e.product_id
    GROUP BY product_id
), 0)) AS costs,

SUM(cost) * 100 / 
(
    SELECT SUM(cost)
    FROM expenses
    WHERE month = 9 AND year = 2019 AND product_id = e.product_id
    GROUP BY product_id
) AS percent_diff,

SUM(cost) AS costs_october,

IFNULL(
(
    SELECT SUM(cost)
    FROM expenses
    WHERE month = 9 AND year = 2019 AND product_id = e.product_id
    GROUP BY product_id
), 0) AS costs_september

FROM expenses e
JOIN products p ON (e.product_id = p.id)
WHERE month = 10 AND year = 2019
GROUP BY product_id
ORDER BY product_id;

But is copy-pasting the same subquery three times really the solution? In theory it requires to run four queries per product. Is there a more elegant way?

Appreciate for any help!

Answer 1

I would address this with conditional aggregation:

select 
    p.description,
    sum(case when e.month = 11 then e.cost else 0 end) costs_november,
    sum(case when e.month = 10 then e.cost else 0 end) costs_october,
    sum(case when e.month = 11 then e.cost else -1 * e.cost end) costs,
    sum(case when e.month = 10 then e.cost else 0 end)
        * 100
        / nullif(
            sum(case when e.month = 11 then e.cost else 0 end),
            0
        ) percent_diff
from expenses e
inner join products p on p.id = e.product_id
where e.year = 2019 and e.month in (10, 11)
goup by e.product_id

You can avoid repeating the same conditional sums by using a subquery (your RDBMS would probably optimize it anyway, but this tends to make the query more readable):

select 
    description,
    costs_november,
    costs_october,
    costs_november - costs_october costs,
    costs_october * 100 / nullif(costs_november, 0) percent_diff
from (
    select 
        p.description,
        sum(case when e.month = 11 then e.cost else 0 end) costs_november,
        sum(case when e.month = 10 then e.cost else 0 end) costs_october
    from expenses e
    inner join products p on p.id = e.product_id
    where e.year = 2019 and e.month in (10, 11)
    goup by e.product_id
) t