Subject RE: [firebird-support] Know nothing - please help
Author Svein Erling Tysvær
>I have a query like this:
>   artikli.a_sif,
>    v1.v_txt,
>    v2.v_txt,
>    coalesce(sum(magkol.k_total),0) total,
>    coalesce(sum(magkol.k_nabcen * magkol.k_total),0) vrednost,
>    coalesce(sum(magkol.k_nabcen * magkol.k_total),0)/ coalesce(sum(magkol.k_total),1) nc,
>    coalesce(sum(izlaz.i_kol),0) P_kom
>from artikli
>   left outer join izlaz on (artikli.a_id = izlaz.i_id)
>   left outer join magkol on (artikli.a_id = magkol.k_art) and magkol.k_mag>0
>   right outer join vrste v1 on v1.v_id=artikli.a_vrs
>   left outer join vrste v2 on v2.v_id=v1.v_nad
>   where artikli.a_sif='R3728'
>group by 1,2,3
>everyting works OK, except that last sum() :
>    coalesce(sum(i zlaz.i_kol),0) P_kom

>It gives me sum result muliplied with number of occurences of that article ID in magkol table.

Well, it may not be what you want, Zoran, but it is what you ask for.

To rather get what you want, you could try changing your query to something like:

with tmp(a_sif, P_kom) as
(select a.a_sif, coalesce(sum(i.i_kol), 0)
from artikli a
left join izlaz I on a.a_id = i.i_id
where a.a_sif='R3728'
and exists(select * from vrste v where v.v_id=a.a_vrs))
coalesce(sum(m.k_total),0) total,
coalesce(sum(m.k_nabcen * m.k_total),0) vrednost,
coalesce(sum(m.k_nabcen * m.k_total),0)/ coalesce(sum(m.k_total),1) nc,
from artikli a
join tmp t on a.a_sif = t.a_sif
join vrste v1 on v1.v_id=a.a_vrs
left join magkol m on a.a_id = m.k_art) and m.k_mag > 0
left join vrste v2 on v2.v_id=v1.v_nad
group by 1, 2, 3, 7

Note that I removed your right join since your where clause logically and practically makes it an inner join. Not knowing your data, I don't know whether the EXISTS can be removed or not or if you can replace some of the left joins with simply join.