MySQL Query for balance (credit / debit) beetween shop

From: Alessandro Arici <alessandro.arici_at_gmail.com>
Date: Wed, 22 Jul 2015 11:08:24 -0700 (PDT)
Message-ID: <1c668e4e-e096-4a3c-bbbe-0529a73e5413_at_googlegroups.com>



Hi to all,
I hope that you can help me to solve my problem...

Let's say we have this table:

+----+------------+----------+--------+
| id | id_account | customer | amount |
+----+------------+----------+--------+
| 1 | 10 | 7 | 10.00 |
| 2 | 10 | 7 | -4 |
| 3 | 10 | 8 | 4.50 |

| 4 | 11 | 7 | -3.00 |
| 5 | 11 | 9 | 7.20 |
| 6 | 11 | 9 | -3.20 |
| 7 | 13 | 10 | 15.00 |
+----+------------+----------+--------+

first id is auto increment (id_transaction).

id_account we can say it's a shop / clerk.

customer is a customer, that spends the amount.

I need to extract only the SUM (if exists) of the amount of customers that have some transaction (amount) in 2 or more id_account.

With that example, I need to have:

id_account 10 = 3 (id 4) (because customer with id 7 used amount in id_account 11 amount charged in id_account 10 [id 1])

id_account 11 = -3 (id 4) and stop, because customer with id 9 is not present in others id_account

id_account 13 = 0 (not 15.00), or not result at all, because it's customer(s) are not present in others id_account.

Because customer 7 has charged 10.00 in id_account 10, and has spent 3.00 (id 4) in id_account 11, id_account 10 is now in debit of 3.00 with id_account 11.

Another example:

Let's say we have 2 shop, and 2 customer.

In my scenario customer 1 (customer in table) can recharge / credit his money account in shop 1 (id_account, in my table), let's say of 10 EURO.

Follow day, customer 1 goes to shop 2 and spend (debit) 3 EURO from his account, charged from shop 1. At the end of month, shop 1 is in debit (need to give some money) to the shop 2. And this balance is 3 EURO.

So, my final sheet / table need to be:

id_account 1 in debit of 3 EURO id_account 2 in credit of 3 EURO.

It is not important that first shop has "gained" 7 EURO (10 EURO charged - 3 EURO in debit with shop 2).

I hope that it's clear.

Thank you guys Received on Wed Jul 22 2015 - 20:08:24 CEST

Original text of this message