On 18 April 2011 20:19, Joerg Bruehe <joerg.bruehe@stripped> 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.bruehe@stripped
> 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
