CODEWITHSUNDEEP
mysqlsqlgrouping
Frederick Marcoux
Frederick Marcoux

Reputation: 2223

MySQL - Calculation of invoice balance grouped by client using 3 tables

I'm currently working on a small client management system and I came accross a feature I can't yet get to work in pure SQL (currently using PHP foreach with lot of lazy loading, so very slow).

I am trying to get the current balance of each clients, so getting the sold of each invoice (by substracting SUM of payments) payment and SUM them. Each clients has invoices in the systems and each invoices can have multiple payments.

Here's my simplified tables for the purpose of being generic:

[Clients]
- id
- name

[Invoices]
- id
- client_id
- amount

[Payments]
- id
- invoice_id
- amount (positive number)

So basically, I want to get this:

+--------------------+----------------------+
| name               | balance              |
+--------------------+----------------------+
| Client 1           | 342,46               |
| Client 2           | 0,00                 |
+--------------------+----------------------+

To get the invoice sold, I need SUM(invoices.amount) - SUM(payments.amount)) but my query isn't working at all. Here's what I got so far:

SELECT DISTINCT
    c.name,
    SUM(x.sold) AS balance
FROM
    clients AS c
RIGHT JOIN
    (
        SELECT SUM(i.invoiceAmount - total_payments) AS sold
        FROM invoices
        WHERE i.client_id = c.id
        RIGHT JOIN (
            SELECT p.id, p.invoice_id, SUM(p.transactionAmount) AS total_payments
            FROM payments AS p
            GROUP BY p.invoice_id
        ) AS p ON p.invoice_id=i.id
        GROUP BY i.client_id
    ) AS x ON x.client_id=c.id
GROUP BY c.name
ORDER BY c.name ASC

Does anybody ever done this before? I never did and it's pretty tricky.

Note: I tried to be as much generic as possible, so it may help other people in a similar situation.

UPDATE: Using @GordonLinoff answer, I was able to get what I wanted using the query he provided me.

select name, SUM(invoices - IFNULL(payments, 0)) as balance
from (
    select name, sum(amount) as invoices, sum(payments) as payments
    from (select c.id, c.name, i.id as invoiceid, i.amount, sum(p.amount) as payments
          from clients c left join
               invoices i
               on c.id = i.client_id left join
               payments p
               on p.invoice_id = i.id
          group by c.id, c.name, invoiceid, i.amount
         ) ci
    group by name
) x

The result is exactly as I expected.

Upvotes: 1

Views: 1207

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Aggregating through hierarchical structures is tricky. Here is one method that joins the tables together and then uses two levels of aggregation:

select name, sum(amount) as invoices, sum(payments) as payments
from (select c.id, c.name, i.id, i.amount, sum(p.amount) as payments
      from clients c left join
           invoices i
           on c.id = i.client_id left join
           payments p
           on p.invoice_id = i.id
      group by c.id, c.name, i.id, i.amount
     ) ci
group by name;

Upvotes: 1

Related Questions