Select from multiple table MS SQL

What I wrong ?

select x1.poslovni_partner as 'Poslovni partner',
CONVERT(decimal(16,2),x1.Nabavka) AS 'Prijem',
CONVERT(decimal(16,2),Izdavanje,0) AS 'Izdavanje'
from(select poslovni_partner, iznos_sa_porezom as Nabavka
from (select poslovni_partner, SUM(ISNULL(iznos_sa_porezom,0)) as iznos_sa_porezom from mp_ulazni_racun_roba_lista union all select poslovni_partner, SUM(ISNULL(iznos_sa_porezom,0)) as iznos_sa_porezom from mp_ulazni_racun_ostalo_lista) a
group by poslovni_partner, iznos_sa_porezom) x1
left join(select poslovni_partner, iznos_sa_pdv as Izdavanje
from(select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from mp_racun_lista union all select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from mp_faktura_lista union all select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from vp_faktura_lista) b
group by poslovni_partner,iznos_sa_pdv) x2
on x1.poslovni_partner = x2.poslovni_partner

Error is

Msg 8120, Level 16, State 1, Line 30
Column 'mp_ulazni_racun_roba_lista.poslovni_partner' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Harsh Gupta6y ago
1. select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv
2. from mp_racun_lista
3. union all
4. select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from mp_faktura_lista
5. union all
6. select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from vp_faktura_lista
In the above part of your code, you can't use SUM() as this is an aggregate function for this you need to add Group by clause with each select statement.

e.g
select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv
            from mp_racun_lista Group by kupac
        union all
        select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from mp_faktura_lista
Group by kupac
        union all
        select kupac as poslovni_partner, SUM(ISNULL(iznos_sa_pdv,0)) as iznos_sa_pdv from vp_faktura_lista
Group by kupac
0