Select from multiple table MS SQL

Question

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.

Amit Gupta · Answer

Please be generally aware that when you use groupby, you must use aggregate function like sum etc

Harsh Gupta · Answer

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 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

Amit Gupta · Answer

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

Goran Bibic · Answer

Example?