1

Here is the data I have:

table agencies:

id code
18 agency_01
6 agency_02

table compensations

id agency_id compensation_category_id amount registered_at
3 6 4 45000.00 2023-01-01
4 6 5 25000.00 2023-01-01
1 18 4 47000.00 2023-01-01
2 18 5 10000.00 2023-01-01

table compensation_categories

id parent_id name
1 null COMPENSATION
2 null DIRECT_STRATEGIC_SUPPORT
3 null INDRECT_STRATEGIC_SUPPORT
4 1 PERSONEL_COST
5 1 FIXED_COMPENSATION

The following code show the correct result:

SELECT a.code,
       SUM(c.amount) AS total_amount
FROM agencies a
LEFT JOIN compensations c ON a.id = c.agency_id
WHERE DATE(c.registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
GROUP BY a.code;

result:

code total_amount
agency_01 57000.00
agency_02 70000.00

while this code does not work:

SELECT a.code,
       COALESCE(SUM(CASE WHEN cc.name = 'COMPENSATION' THEN c.amount END), 0) AS COMPENSATION,
       COALESCE(SUM(CASE WHEN cc.name = 'DIRECT_STRATEGIC_SUPPORT' THEN c.amount END), 0) AS DIRECT_STRATEGIC_SUPPORT,
       COALESCE(SUM(CASE WHEN cc.name = 'INDRECT_STRATEGIC_SUPPORT' THEN c.amount END), 0) AS INDRECT_STRATEGIC_SUPPORT
FROM agencies a
LEFT JOIN compensations c ON a.id = c.agency_id
LEFT JOIN compensation_categories cc ON cc.id = c.compensation_category_id
WHERE DATE(c.registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
GROUP BY a.code;

result:

code COMPENSATION DIRECT_STRATEGIC_SUPPORT INDRECT_STRATEGIC_SUPPORT
agency_01 0.00 0.00 0.00
agency_02 0.00 0.00 0.00

The amounts from the first query should fall under "COMPENSATION" column of the second query, but it shows zero. What am I doing wrong?

Thank you!

0

1 Answer 1

0

This worked for me:

SELECT a.code,
       COALESCE(SUM(CASE WHEN parent.name = 'COMPENSATION' THEN c.amount END), 0) AS COMPENSATION,
       COALESCE(SUM(CASE WHEN parent.name = 'DIRECT_STRATEGIC_SUPPORT' THEN c.amount END), 0) AS DIRECT_STRATEGIC_SUPPORT,
       COALESCE(SUM(CASE WHEN parent.name = 'INDRECT_STRATEGIC_SUPPORT' THEN c.amount END), 0) AS INDRECT_STRATEGIC_SUPPORT
FROM agencies a
LEFT JOIN compensations c ON a.id = c.agency_id
LEFT JOIN compensation_categories cc ON cc.id = c.compensation_category_id
LEFT JOIN compensation_categories parent ON parent.id = cc.parent_id
WHERE DATE(c.registered_at) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
GROUP BY a.code;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.