I have a union of 3 tables for calculating some balance and I need to get the running sum of that balance,
but I can't use partition over,because I must do it with the sql query that is gonna work in access too.
My problem is that I cannot use join on a alias subquery, it won't work.
This is my code so far:
SELECT korisnik_id,imePrezime,datum,Dug,Pot,(Dug-Pot) AS Balance
FROM(
SELECT korisnik_id, k.imePrezime, r.datum,SUM(IIF(u.jedinstven = 1, r.cena, k.kvadratura * r.cena)) AS Dug,'0' AS Pot
FROM Racun r
INNER JOIN Usluge u on r.usluga_id = u.ID
INNER JOIN Korisnik k on r.korisnik_id = k.ID
WHERE korisnik_id =1 AND r.zgrada_id = 1 and r.mesec = 1 and r.godina = 2017
group by korisnik_id,k.imePrezime,r.datum
UNION ALL
SELECT korisnik_id, k.imePrezime, rp.datum, SUM(IIF(u.jedinstven = 1, rp.cena, k.kvadratura * rp.cena)) AS Dug,'0' AS Pot
FROM RacunP rp
INNER JOIN Usluge u on rp.usluga_id = u.ID
INNER JOIN Korisnik k on rp.korisnik_id = k.ID
WHERE korisnik_id =1 AND rp.zgrada_id = 1 and rp.mesec = 1 and rp.godina = 2017
group by korisnik_id,k.imePrezime,rp.datum
UNION ALL
SELECT uu.korisnik_id, k.imePrezime, uu.datum,'0' AS Dug, SUM(uu.iznos) AS Pot
FROM UnosUplata uu
INNER JOIN Korisnik k on uu.korisnik_id = k.ID
WHERE korisnik_id =1
group by uu.korisnik_id, k.imePrezime, uu.datum
) AS a
ORDER BY korisnik_id
How can I use a in a join to get the running total?
YOu can in SQL server, not sure about access. Access SQL is kind of in its own universe.
There are problems in your query though. YOu set aliases on most tables, but don't consistently use them in column references, which makes it hard to read, e.g.
WHERE korisnik_id =1
Which table are you referring to here? I can't tell since this is not a 2-part column name. Always use 2-part names.
I am refering at each of the union tables to select only rows where korisnik_id(user id field) is 1
As you didn't provide ddl and sample data, I can't guarantee this will work:
Query
select a.korisnik_id
,a.imeprezime
,a.datum
,a.dug
,a.pot
,a.balance
,sum(b.balance) as balance_sum
from (select a.korisnik_id
,k.imeprezime
,a.datum
,sum(a.cena*iif(a.jedinstven=1,1,k.kvadratura)) as dug
,sum(a.iznos) as pot
,sum(a.cena*iif(a.jedinstven=1,1,k.kvadratura))-sum(a.iznos) as balance
from (select r.korisnik_id
,r.datum
,u.jedinstven
,r.cena
,0 as uznos
from (select korisnik_id
,usluga_id
,datum
,cena
from racun
where korisnik_id=1
and zgrada_id=1
and mesec=1
and godina=2017
union all
select korisnik_id
,usluga_id
,datum
,cena
from racunp
where korisnik_id=1
and zgrada_id=1
and mesec=1
and godina=2017
) as r
inner join usluge as u
on u.id=r.usluga_id
union all
select korisnik_id
,datum
,u.jedinstven
,0 as cena
,iznos
from unouplata as u
where korisnik_id=1
) as a
inner join korisnik as k
on k.id=a.korisnik_id
group by a.korisnik_id
,k.imeprezime
,a.datum
) as a
/*****
* exact copy of above except not needed fields (dug&pot)
*/
inner join (select a.korisnik_id
,k.imeprezime
,a.datum
/* not needed ,sum(a.cena*iif(a.jedinstven=1,1,k.kvadratura)) as dug*/
/* not needed ,sum(a.iznos) as pot*/
,sum(a.cena*iif(a.jedinstven=1,1,k.kvadratura))-sum(a.iznos) as balance
from (select r.korisnik_id
,r.datum
,u.jedinstven
,r.cena
,0 as uznos
from (select korisnik_id
,usluga_id
,datum
,cena
from racun
where korisnik_id=1
and zgrada_id=1
and mesec=1
and godina=2017
union all
select korisnik_id
,usluga_id
,datum
,cena
from racunp
where korisnik_id=1
and zgrada_id=1
and mesec=1
and godina=2017
) as r
inner join usluge as u
on u.id=r.usluga_id
union all
select korisnik_id
,datum
,u.jedinstven
,0 as cena
,iznos
from unouplata as u
where korisnik_id=1
) as a
inner join korisnik as k
on k.id=a.korisnik_id
group by a.korisnik_id
,k.imeprezime
,a.datum
) as b
/*
* exact copy of above except not needed fields (dug&pot)
*****/
on b.imeprezime=a.imeprezime
and b.korisnik_id<=a.korisnik_id
and b.datum<=a.datum
group by a.korisnik_id
,a.imeprezime
,a.datum
,a.dug
,a.pot
,a.balance
order by a.korisnik_id
;
1 Like
still, always use 2-part names.
1 Like
Ok, thanks for the advice!