0

I have the tables

costs:

id type year amount
1 A 2020 400
2 A 2020 200
3 A 2021 100
4 B 2021 100
5 B 2021 200

discounts:

id id_cost amount
1 4 10
2 1 40
3 1 70
4 2 30
5 2 50

I need amountSum and discountSum to be the sums grouped by year

type year amountSum discountSum
A 2020 600 190
A 2021 100 0
B 2021 300 10

What I tried:

SELECT
    costs.type,
    costs.year,
    COALESCE (SUM(costs.amount),0) AS amountSum,
    COALESCE (SUM(discounts.amount),0) AS discountSum
FROM
    costs
LEFT JOIN discounts ON costs.id = discounts.id_cost
GROUP BY
    costs.type,
    costs.year
ORDER BY
    costs.type ASC;

But it does not return amountSum correctly if there is more than one discount for the same year, for example: A - 2020: 1200.

type year amountSum discountSum
A 2020 1200 190
A 2021 100 0
B 2021 300 10

How can I do it?

UPDATE: solution

SELECT type, year, SUM(amount) AS amountSum, COALESCE(SUM(d.discountSum),0) AS discountSum
FROM costs
LEFT JOIN (SELECT id_cost, COALESCE (SUM(amount),0) AS discountSum FROM discounts GROUP BY id_cost ) D ON costs.id = D.id_cost
GROUP BY type, year;
bonnegnu
  • 175
  • 3
  • 12
  • 1
    https://stackoverflow.com/help/minimal-reproducible-example –  Jul 26 '21 at 22:29
  • What rule determines which discount amount to use? The one with the higher discount ID or the higher discount amount? By your sample data, probably just the `Max(Amount)` for each id_cost. Build aggregate query to pull Max(Amount) for each id_cost and join that query to Costs table. – June7 Jul 26 '21 at 23:15
  • @radocaw I have posted the solution in the answer. Thanks for the advice, if you can enable my account, I want to continue contributing to the community. Thanks! – bonnegnu Jul 28 '21 at 00:42

0 Answers0