SQLTeam.com | Weblogs | Forums

How to use alias of a subquery in a where clause to get the running total?


#1

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?


#2

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.


#3

I am refering at each of the union tables to select only rows where korisnik_id(user id field) is 1


#4

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
;

#5

still, always use 2-part names.


#6

Thank you!


#7

Ok, thanks for the advice!