Daniel Samuels Daniel Samuels - 6 months ago 6
SQL Question

Getting the sum of two columns from two different tables

I have two MySQL tables, with the following structure (I have removed irrelevant columns).

mysql> DESCRIBE `edinners_details`;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| details_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| details_pupil_id | int(11) unsigned | NO | | NULL | |
| details_cost | double unsigned | NO | | NULL | |
+------------------+------------------+------+-----+---------+----------------+

mysql> DESCRIBE `edinners_payments`;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| payment_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| payment_pupil_id | int(11) unsigned | NO | | NULL | |
| payment_amount | float unsigned | NO | | NULL | |
+------------------+------------------+------+-----+---------+----------------+


The way the system works is that you order a meal and each meal has a cost, each of these orders are stored in
edinners_details
. An example row would be as follows:

mysql> SELECT * FROM `edinners_details` LIMIT 1;
+------------+------------------+--------------+
| details_id | details_pupil_id | details_cost |
+------------+------------------+--------------+
| 1 | 18343 | 25 |
+------------+------------------+--------------+


Typically people will pay for these meals in bulk - if they have £40 worth of meals over the course of 20 days, they'll pay that off at the end of the month. Every time they pay, a new row goes into the
edinners_payments
table, an example row for this would be as follows:

mysql> SELECT * FROM `edinners_payments` LIMIT 1;
+------------+------------------+----------------+
| payment_id | payment_pupil_id | payment_amount |
+------------+------------------+----------------+
| 1 | 18343 | 20 |
+------------+------------------+----------------+


So from these two rows we can see that this person is current £5 in debt - they've had a £25 meal and only paid £20. Over time there will be many rows for each of the users of the system, I can easily work out how much worth of food they've had by doing a simple query such as

SELECT SUM(`details_cost`) AS `meal_total`
FROM `edinners_details`
WHERE `details_pupil_id` = '18343';


Then to get the amount of money they have paid, I simply do this query:

SELECT SUM(`payment_amount`) AS `payment_total`
FROM `edinners_payments`
WHERE `payment_pupil_id` = '18343';


My ultimate goal is to be able to see who owes the most money, but to loop every user of my
users
table and run these two queries for them, I believe it would be quite slow, so ideally what I would like to do is combine the two above queries into one, and perhaps an additional column which would be (
meal_total
-
payment_total
) which would give me the amount owed. I've tried a few methods to make this work, including joins and sub-queries but they all seem to repeat every relevant line from the
edinners_details
for each of the
edinners_payments
line - so if there were 3 details and 4 payments, you would have 12 rows pulled out which means doing a SUM() on the columns gives me a value which far exceeds what it should be. A good way to demonstrate this would be to run this query:

SELECT * FROM (
SELECT `details_cost` AS `cost`
FROM `edinners_details`
WHERE `details_pupil_id` = '18343'
GROUP BY `details_id`
) AS `details`, (
SELECT `payment_amount` AS `amount`
FROM `edinners_payments`
WHERE `payment_pupil_id` = '18343'
GROUP BY `payment_id`
) AS `payment`;


which gives me the following result:

+------+--------+
| cost | amount |
+------+--------+
| 2.5 | 20 |
| 2.5 | 6 |
| 2.5 | 3 |
| 2.5 | 1200 |
| 2.5 | 20 |
| 2.5 | 6 |
| 2.5 | 3 |
| 2.5 | 1200 |
| 2.5 | 20 |
| 2.5 | 6 |
| 2.5 | 3 |
| 2.5 | 1200 |
| 2.5 | 20 |
| 2.5 | 6 |
| 2.5 | 3 |
| 2.5 | 1200 |
| 2.5 | 20 |
| 2.5 | 6 |
| 2.5 | 3 |
| 2.5 | 1200 |
+------+--------+


Adding the SUM into this, as such:

SELECT SUM(`details`.`cost`) AS `details_cost`, SUM(`payment`.`amount`) AS `payment_total` FROM (
SELECT `details_cost` AS `cost`
FROM `edinners_details`
WHERE `details_pupil_id` = '18343'
GROUP BY `details_id`
) AS `details`, (
SELECT `payment_amount` AS `amount`
FROM `edinners_payments`
WHERE `payment_pupil_id` = '18343'
GROUP BY `payment_id`
) AS `payment`;


Gives me the following result:

+--------------+---------------+
| details_cost | payment_total |
+--------------+---------------+
| 50 | 6145 |
+--------------+---------------+


If this was working,
details_cost
would be 12.5 and
payment_total
would be 1229, but that is not the case. You can clearly see the repetition in the above result, I apologise that all the costs are 2.5, it makes it a little less obvious, but they are 5 seperate meal orders with 4 payments having been made. Does anyone know how I would go about getting the SUM() of the meal order costs and the SUM() of the payments at the same time?

Thanks

Answer

The following works for me, although it looks ugly. In a MySQL DB:

SELECT
    t1.p_id, t1.cost, t2.amount
FROM
    (SELECT
        details_pupil_id AS p_id, SUM(details_cost) AS cost
     FROM
        edinners_details
     GROUP BY
        details_pupil_id) t1,
    (SELECT
        payment_pupil_id AS p_id, SUM(payment_amount) AS amount
     FROM
        edinners_payments
     GROUP BY
        payments_pupil_id) t2
WHERE
    t1.p_id = t2.p_id

/* Getting pupils with dinners but no payment */
UNION
    SELECT
        details_pupil_id, SUM(details_cost) cost, 0
    FROM
        edinners_details
    WHERE
        details_pupil_id NOT IN (SELECT DISTINCT payment_pupil_id FROM edinners_payments)
    GROUP BY
        details_pupil_id

/* Getting pupils with payment but no dinners */
UNION
    SELECT
        payment_pupil_id, 0, SUM(payment_amount)
    FROM
        edinners_payments
    WHERE
        payment_pupil_id NOT IN (SELECT DISTINCT details_pupil_id FROM edinners_details)
    GROUP BY
        payment_pupil_id