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

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