Hallo everybody!
Ants Pants wrote:
> 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 |
> +---------------------------------------------+
AFAICS, you are missing the equality condition on the currency between
the subquery and the other tables. This would explain why the 70 is
subtracted not only from the BRL value but also from the EUR.
Off-hand, I have no explanation for the doubling of the sums, but I have
never used subqueries in the FROM clause.
Others might know more about this, but telling the version you are using
might be helpful for them.
That said, IMO you are doing it much more complicated than necessary:
As your "invitations" table that lists the pledges also has a field
"paid", it seems you could calculate the amounts outstanding in the same
way as those paid, just changing the condition on "paid":
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;
Of course, details will depend on what you enter in "paid", and you must
take care of NULL values.
HTH,
Joerg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
