MySQL - Subtracting SUMs with tricky joins and a group by

Question

I have two tables that link a bill to the charges on the bill, and a third table that links a payment to the charge. I am trying to determine if for each bill, the charge was paid in full.

There can be either one, two bill versions.
A bill version does not necessarily have corresponding charges.
If there are charges and payments, there can be any number of payments towards any number of charges.

The records in the first table 'bill_charges', show that for each bill_nbr there is an associated bill_id (one with two bill_ids, and thus multiple versions).

CREATE TABLE bill_charges (
    `bill_nbr`   varchar(30)   NOT NULL,
    `bill_id`    int(11)       NOT NULL,
    `version`    enum('1','2') NOT NULL DEFAULT '1'
);

INSERT INTO bill_charges (bill_nbr, bill_id, version)
VALUES ('50', 500, '1'),
       ('50', 501, '2'),
       ('60', 600, '1');

The records in the second table 'charges', show that for the second bill_id in 'bill_charges' there were multiple charges of different types and dollar amounts.

CREATE TABLE charges (
    `charge_id`      int(11) NOT NULL AUTO_INCREMENT,
    `charge_type_id` int(11) NOT NULL,
    `bill_id`        int(11) NOT NULL,
    `charge_amt`     decimal(13,2) NOT NULL DEFAULT '0.00'
);

INSERT INTO charges (charge_id, charge_type_id, bill_id, charge_amt)
VALUES (10, 100, 501,  15.00),
       (11, 100, 501,  -5.00),
       (12, 200, 501,  50.00),
       (13, 300, 501,  -5.00),
       (14, 300, 501,  -5.00),
       (15, 300, 501, -25.00);

The records in the third table 'payments', show that three of the charges (13, 14, 15) were used to pay two of the other charges (10, 12).

CREATE TABLE payments (
    `payment_id`     int(11) NOT NULL AUTO_INCREMENT,
    `pays_charge_id` int(11) NOT NULL,
    `charge_id`      int(11) NOT NULL,
    `payment_amt`    decimal(13,2) NOT NULL DEFAULT '0.00'
);

INSERT INTO payments (payment_id, pays_charge_id, charge_id, payment_amt)
VALUES (20, 13, 10,  -5.00),
       (21, 14, 10,  -5.00),
       (20, 15, 12, -25.00);

To put this into context, a customer paid for multiple items and then returned some. For this particular scenario, the charges for the first version were wiped out with the return, so there are only charges on the latest bill version (#2).

The charges show that there was a charge that was reduced from $15 to $10 (the SUM of charge_ids 10 and 11). There was a second charge for $50 (charge_id = 12). Finally, by looking at the payments table you can see that charge_ids 13, 14, and 15 weren't really charges at all, but payments made towards the other two charges.

My goal is to find out the remaining balance for one particular charge type (100), by looking at all payments made towards the charge on each bill_id. I also want to know if there were no charges or payments for that same charge type on other bills. My end result should be:

bill_nbr  bill_id  version  remaining_balance
50        500      1        NULL
50        501      2        0
60        600      1        NULL

My initial approach starts by getting all bill_charges information and grouping it properly. Next, I add in the charge information via LEFT JOIN and SUM the amount for charge_type_id = 100.

SELECT    bills.bill_nbr, bills.bill_id, 
          bills.version, SUM(charges.amount) AS total_charges
FROM      bill_charges AS bills
LEFT JOIN charges
          ON  charges.bill_id        = bills.bill_id
          AND charges.charge_type_id = 100
GROUP BY bill_id, version;

This gives me a good start, by showing the SUM of the charges for that type without the payments.

bill_nbr  bill_id  version  total_charges
50        500      1        NULL
50        501      2        10
60        600      1        NULL

Here's where I run into problems. If I LEFT JOIN again to the payments table and SUM the payments, the total remaining_balance is -$5.00 instead of $0.

SELECT    bills.bill_nbr, bills.bill_id, 
          bills.version,
          SUM(charges.amount) - SUM(payments.amount) AS remaining_balance 
FROM      bill_charges AS bills
LEFT JOIN charges
          ON  charges.bill_id        = bills.bill_id
          AND charges.charge_type_id = 100
LEFT JOIN payments
          ON payments.charge_id = charges.charge_id
GROUP BY bill_id, version;

The only way I can make this work is with a messy query. In the real world, all three tables are quite large in size - and this causes performance issues.

SELECT    x.bill_nbr, x.bill_id, x.version,
          x.total - y.total AS remaining_balance 
FROM      (
             SELECT    t1.bill_nbr, t1.bill_id, t1.version
                       SUM(t2.amount) AS total
             FROM      bill_charges AS t1
             LEFT JOIN charges      AS t2
                       ON  t2.bill_id = t1.bill_id
                       AND t2.charge_type_id = 100
             GROUP BY  t1.bill_id, t1.version
) x
LEFT JOIN (
             SELECT    t1.bill_nbr, t1.bill_id, t1.version
                       SUM(t3.amount) AS total
             FROM      bill_charges AS t1
             LEFT JOIN charges      AS t2
                       ON  t2.bill_id = t1.bill_id
                       AND t2.charge_type_id = 100
             LEFT JOIN payments     AS t3
                       ON t3.charge_id = t2.charge_id
             GROUP BY  t1.bill_id, t1.version
) y
          ON  x.bill_id = y.bill_id
          AND x.version = y.version
GROUP BY  x.bill_id, x.version;

What is the question? PS [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) PS [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) — philipxy, Aug 05 '22 at 05:52
When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Aug 05 '22 at 05:56

score 0 · Answer 1 · answered Aug 05 '22 at 07:55

From your second query, two modifications were made:

Line 3 : Sum of charge amount + Sum of payment amount (previously was subtraction)
Line 8 : Left join a subquery (previously was payments table)

You may try this solution, see db<>fiddle:

SELECT    bills.bill_nbr, bills.bill_id, 
          bills.version,
          SUM(charges.charge_amt) + SUM(payments.payment_amt) AS remaining_balance 
FROM      bill_charges AS bills
LEFT JOIN charges
          ON  charges.bill_id        = bills.bill_id
          AND charges.charge_type_id = 100
LEFT JOIN (
             SELECT    charge_id, SUM(payment_amt) AS payment_amt
             FROM      payments
             GROUP BY  charge_id
) payments
          ON payments.charge_id = charges.charge_id
GROUP BY  bills.bill_nbr, bills.bill_id, 
          bills.version

MySQL - Subtracting SUMs with tricky joins and a group by

1 Answers1