List:General Discussion« Previous MessageNext Message »
From:Ants Pants Date:April 18 2011 6:34pm
Subject:Re: Subqueries in the FROM Clause
View as plain text  
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

Thread
Subqueries in the FROM ClauseAnts Pants18 Apr
  • Re: Subqueries in the FROM ClauseJoerg Bruehe18 Apr
    • Re: Subqueries in the FROM ClauseAnts Pants18 Apr