On 18 April 2011 20:19, Joerg Bruehe <joerg.bru...@oracle.com> wrote:
> 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 > Joerg, How embarrassing!! That's what you get for not taking breaks and having blinker vision. I'm such a fool!! Right now, I am the colour of my shirt. A very bright red!! Have a nice evening
