CODEWITHSUNDEEP
mysqlsql
mdakovac
mdakovac

Reputation: 61

SUM() over a many-to-many relation in MYSQL

I have tables stanje and transakcija in a many-to-many relation, as shown in the image:

tables

I need a MYSQL clause that returns all rows in stanje joined by a SUM() of every transakcija.iznos connected to a given stanje .

So far I have tried 

select SUM(t.iznos) 
    from transakcija t 
where transakcija_id in 
    (select transakcija_id from stanje_transakcija where stanje_id = ?)

which returns the SUM() correctly when given a stanje_id, but have no idea how to proceed, since I need sums for all rows in stanje.

Edit: added example output

------------------------------------
| stanje_id | naziv | SUM(t.iznos) |
------------------------------------
|     1     |   a   |     125.2    | 
|     2     |   b   |     -42.2    |
------------------------------------

Upvotes: 2

Views: 866

Answers (1)

D-Shih
D-Shih

Reputation: 46229

If I understand correctly, you need to use JOIN in thoes tables by transakcija_id column and stanje_id column.

From your expect result you can try to use SUM with GROUP BY

select t2.stanje_id,t2.naziv,SUM(t.iznos) 
from transakcija t 
INNER JOIN stanje_transakcija t1 on t.transakcija_id = t1.transakcija_id
INNER JOIN stanje t2 on t2.stanje_id = t1.stanje_id
GROUP BY t2.stanje_id,t2.naziv

Upvotes: 2

Related Questions