Difference of sums from two different tables with grouping statement in MySql

Question

I'm creating a portal with customers with sell/payment options, where all sells are in one table and all payments in another. For customer status table I need to get a sum of all payments per client and all sells per client plus difference between them.

Sample tables:

table clients

====================================
id  client_name
====================================
1   School "Mike Jordan"
2   Car wash "Blah blah"

table purchasing

===================================
id  client_id  amount
===================================
1   1          1000
2   1          500
3   2          800
4   2          800

table payments

===================================
id  client_id  amount
1   1          300
2   2          200
3   2          200

I have tried with inner join and grouping but this is not getting sum properly, always larger one, like it is counting things I don't want (with no total, just trying to get sums:

select clients.`client_name `,
sum(purchasing.amount) as SOLD,
sum(payments.amount) as PAID 
from clients
inner join payments on payments.client_id=clients.id
inner join purchasing on purchasing.client_id=clients.id
group by clients.id

where I got results different (all bigger).

client_name           SOLD  PAID
================================
School "Mike Jordan"  1500  600
Car wash "Blah blah"  3200  800

If I try to add grouping by payments.id and/or purchasing.id a got more than two results... I know that there is a grouping problem but I really have no idea where it is.

And I wanted to add new column which is just difference between SOLD and PAID, but server generates error with message that SOLD and PAID are not in a field list.

Any idea how to accomplish this with a single query?

D-Shih · Accepted Answer · 2018-08-22T22:20:29.093

You can try this. use UNION ALL to combine purchasing and payments table then join to clients.

Next step do SUM and group by

create table clients(
   id int,
   client_name varchar(50)
);

insert into clients values (1,'School "Mike Jordan"');
insert into clients values (2,'Car wash "Blah blah"');


create table purchasing(
  id int,
   client_id int,
   amount int
);


insert into purchasing values (1,1,1000);
insert into purchasing values (2,1,500);
insert into purchasing values (3,2,800);
insert into purchasing values (4,2,800);

create table payments(
  id int,
   client_id int,
   amount int
);
insert into payments values (1,1,300);
insert into payments values (2,2,200);
insert into payments values (3,2,200);

Query 1:

select c.client_name,
       sum(SOLD) as SOLD,
        sum(PAID) as PAID
from (
    SELECT client_id,amount as SOLD,0 as PAID  
    FROM purchasing
    UNION ALL 
    SELECT client_id,0,amount   
    FROM payments
)t1 INNER JOIN clients c on t1.client_id = c.id
group by c.client_name

Results:

|          client_name | SOLD | PAID |
|----------------------|------|------|
| Car wash "Blah blah" | 1600 |  400 |
| School "Mike Jordan" | 1500 |  300 |

just figure out that this one is better solution :) If there is nothing paid, just sold, it will not display in the list at all that client, only if there are sold and paid items. In this solution it will display regardless if there are just sold or just paid items per client — Војин Петровић, Aug 24 '18 at 13:37

dandarc · Answer 2 · 2018-08-22T22:16:22.557

0

You need to summarize your tables before doing joins.

SELECT clients.`client_name`
    ,purchasing_sum.SOLD
    ,payments_sum.PAID
FROM clients
INNER JOIN (
    SELECT client_id
        ,sum(amount) as PAID
    FROM payments
    GROUP BY client_id
    ) payments_sum ON payments_sum.client_id = clients.id
INNER JOIN (
    SELECT client_id
        ,sum(amount) as SOLD
    FROM purchasing
    GROUP BY client_id
    ) purchasing_sum ON purchasing_sum.client_id = clients.id
GROUP BY clients.id

edited Aug 22 '18 at 22:16

answered Aug 22 '18 at 22:02

dandarc

678
3
7

purchasing_sum.SOLD is not in a field list... – Војин Петровић Aug 22 '18 at 22:11
Fixed it - I was in a hurry and botched the subqueries. – dandarc Aug 22 '18 at 22:16
SELECT clients.`client_name` , payments_sum.PAID , purchasing_sum.SOLD FROM clients INNER JOIN ( SELECT client_id, sum(amount) PAID from payments GROUP BY client_id ) payments_sum ON payments_sum.client_id = clients.id INNER JOIN ( SELECT client_id, sum(amount) SOLD from purchasing GROUP BY client_id ) purchasing_sum ON purchasing_sum.client_id = clients.id GROUP BY clients.id this one works – Војин Петровић Aug 22 '18 at 22:16
And total is working fine, just added (payments_sum.PAID-purchasing_sum.SOLD) as TOTAL ... and it works! Thanks man, you saved my dayS! – Војин Петровић Aug 22 '18 at 22:18
No problem - the key thing is to understand why. The original join contains multiple payments for the same ID and multiple sales. So that one case, you've got 2 sales X 2 purchases, that's 4 rows - sum that up and you've got literally double what you're expecting for both fields. Need to knock the 2 tables down to 1 row per ID, then you can join them safely. – dandarc Aug 22 '18 at 22:21
Understood, thanks – Војин Петровић Aug 22 '18 at 22:40

Difference of sums from two different tables with grouping statement in MySql

2 Answers2