Re: MySQL Query for balance (credit / debit) beetween shop

On 22-7-2015 20:08, Alessandro Arici wrote:
> 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.
>

untested:

SELECT
    t1.id_account,
    t1.customer,
    sum(t1.amount)
FROM table t1
GROUP BY t1.id_account, t1.customer
WHERE (SELECT count(t2.id_account)

    FROM table t2
    WHERE customer=t1.customer) >= 2

should produce:

id_account,customer,SUM

10, 7, 6
10, 8, 4.5
11, 7, -3
11, 9, 4
13, 10, 15