CODEWITHSUNDEEP
mysqlsql
Satish
Satish

Reputation: 726

How to get sum of amount from multiple accounts which return by join

I have 3 tables like:

owner_details:-

 owner_id   owner_name
    ---------------------
    1            A
    2            B    
    3            C
    -------------------

vehicle_owner:-

    v_id  vehicle_id   owner_id
    -------------------------
    1      1            1
    2      2            2
    3      4            1
    4      3            1 
    5      5            3

transaction:-

id   v_id         amount   transaction_type
--------------------------------
1      1           100       0
2      2           250       1
3      1           150       1
4      3           450       1
5      1           200       0
6      4           300       1
7      5           150       0
8      5           200       1

transaction_type= 0 then (-) transaction_type=1 then (+)

Owner A (1) have 3 vehicles with v_id (1,3,4) in table vehicle_owner.

v_id (1,3,4) have 5 entries in table transaction (1,3,4,5,6) with sum of amount 600 (-100+150+450-200+300)

Now I want listing like this:-.

owner_id   owner_name  amount
    ---------------------
    1            A       600
    2            B       250
    3            C       50
    -------------------

Upvotes: 0

Views: 48

Answers (3)

Satish
Satish

Reputation: 726

It works for me

SELECT od.owner_id, 
           od.owner_name, 
           Sum(t.amount) AS amount 
    FROM   owner_details od 
           INNER JOIN vehicle_owner vo 
                   ON od.owner_id = vo.owner_id 
           INNER JOIN (SELECT v_id, 
                              Coalesce(Sum(CASE 
                                             WHEN type = 0 THEN -amount 
                                             ELSE +amount 
                                           end), 0.0) AS amount 
                       FROM   `transaction` 
                       GROUP  BY v_id) t 
                   ON vo.v_id = t.v_id 
    GROUP  BY od.owner_id

Thanks Sebastian Brosch for quick response !!!

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

You can use the following query:

SELECT od.owner_id, od.owner_name, SUM(t.amount) AS amount 
FROM owner_details od INNER JOIN vehicle_owner vo ON od.owner_id = vo.owner_id 
    INNER JOIN `transaction` t ON vo.v_id = t.v_id 
GROUP BY od.owner_id

If you want to use the additional transaction_type you can use the following:

SELECT od.owner_id, od.owner_name, SUM(CASE WHEN t.transaction_type = 0 THEN t.amount * -1 ELSE t.amount END) AS amount 
FROM owner_details od INNER JOIN vehicle_owner vo ON od.owner_id = vo.owner_id 
    INNER JOIN `transaction` t ON vo.v_id = t.v_id 
GROUP BY od.owner_id

demo: http://sqlfiddle.com/#!9/c5f8d/1/1

Upvotes: 2

cdaiga
cdaiga

Reputation: 4937

Try this:

SELECT A.owner_id, A.owner_name, SUM(IFNULL(amount,0)) AMOUNT
FROM owner_details A LEFT JOIN
vehicle_owner B
ON A.owner_id=B.owner_id
LEFT JOIN `transaction` C
ON C.v_id=B.v_id
GROUP BY A.owner_id, A.owner_name;

Upvotes: 0

Related Questions