Hello All,
Tables:
# relevant fields
invitations: donation_pledge, paid (boolean), currency_id
currencies: code
I am trying to subtract the paid amounts from the amounts pledged using a
subquery in the FROM clause but am having problems and am going blind. Plus,
My SQL is weak at present.
I was hoping a SQL ninja could have a look for me and tell me where I'm
going wrong.
I hope the following formats nicely for you to see what I've done ....
This shows the amounts pledged grouped by (currency) code
SELECT SUM(i.donation_pledge), c.code
FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
WHERE i.meeting_id = 934311021
GROUP BY c.code;
+------------------------+------+
| sum(i.donation_pledge) | code |
+------------------------+------+
| 11170 | BRL |
| 2997 | EUR |
+------------------------+------+
This shows the amounts paid grouped by (currency) code
SELECT SUM(i.donation_pledge), c.code
FROM invitations i LEFT JOIN currencies c ON i.currency_id = c.id
WHERE i.meeting_id = 934311021 AND paid = true
GROUP BY c.code;
+------------------------+------+
| sum(i.donation_pledge) | code |
+------------------------+------+
| 70 | BRL |
| 999 | EUR |
+------------------------+------+
And this is supposed to show the amounts outstanding but it has doubled the
values and subtracted 70 from each each value (the BRL currency code amount)
SELECT sum(donation_pledge) - paid_donation_pledge
FROM (SELECT i2.meeting_id, sum(donation_pledge) AS paid_donation_pledge
FROM invitations i2 LEFT JOIN currencies c2 ON
i2.currency_id = c2.id
WHERE i2.meeting_id = 934311021 AND i2.paid = true
GROUP BY c2.code ) AS i2 LEFT JOIN invitations i ON i2.meeting_id
= i.meeting_id
LEFT JOIN currencies c
ON i.currency_id = c.id
GROUP BY c.code;
+---------------------------------------------+
| sum(donation_pledge) - paid_donation_pledge |
+---------------------------------------------+
| 22270 |
| 5924 |
+---------------------------------------------+
Many thanks in advance
-ants
