I have 2 tables:
Table 1 "contract" is as the following (sample only):
| contract_id | contract_status |
|---|---|
| 111 | Active |
| 122 | Active |
| 133 | Finished |
| 144 | Active |
| 155 | Finished |
contract_status values are: Active and Finished
Table 2 "ledger" is as the following (sample only):
| ledger_id | contract_id | ledger_status | amount |
|---|---|---|---|
| 1gh | 111 | WAIVED | 450 |
| 2uk | 111 | PAID | 0 |
| 2jz | 111 | WAIVED | 200 |
| 4bc | 122 | PAID | 0 |
| 5jw | 122 | UNPAID | 150 |
| 3xs | 133 | PAID | 0 |
| 9kd | 133 | WAIVED | 250 |
| 7bf | 144 | WAIVED | 100 |
| 8aq | 155 | UNPAID | 700 |
One contract can have a lot of ledger_id
How can I calculate the total amount that has been waived for each contract status (active and finished)?
Expected output
| contract_status | total_amount_waived |
|---|---|
| Active | 750 |
| Finished | 250 |
CodePudding user response：
We can try a join aggregation approach:
SELECT c.contract_status, COALESCE(SUM(l.amount), 0) AS total_amount_waived
FROM contract c
LEFT JOIN ledger l
ON l.contract_id = c.contract_id AND
l.ledger_status = 'WAIVED'
GROUP BY c.contract_status;