Select from multiple table MS SQL

Question

1.00/5 (1 vote)

See more:

What I wrong ?

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.

What I have tried:

SQL

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

Posted 10-Nov-19 3:31am

Goran Bibic

Updated 10-Nov-19 4:53am

Richard MacCutchan

v2

Add a Solution

Comments

Goran Bibic 10-Nov-19 9:59am

Now is updated by Richard MacCutchan

Error is Msg 8120, Level 16, State 1, Line 44
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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2019-11-10T04:13:00

Solution 1

See the excellent, but somewhat lenghty explanation by OriginalGriff here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Posted 10-Nov-19 4:13am

RickZeeland

Comments

OriginalGriff 10-Nov-19 10:22am

"Lengthy"?
"Lengthy"?

That was the short version! :laugh:

RickZeeland 10-Nov-19 10:27am

Excuse me for my Dutch way of spelling :)

MadMyche · Answer 2 · 2019-11-10T04:53:00

Solution 2

My recommendations would be to try this out in layers as you have a few sub-queries in the code, and then work your way up the line. You may even want to encapsulate the various sub-queries as views and then join them in the parent query.

In this chunk, we have the parent query referring to subquery(1) which in turn is calling subquery(2).
It appears that the aggregate functions are in Subquery(2), however; the GROUP BY clause is outside of that loop on Subquery(1)

SQL

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

Posted 10-Nov-19 4:53am

MadMyche

Comments

Goran Bibic 10-Nov-19 12:23pm

I try this and work but just 1 row x1.poslovni_partner

Poslovni partner Ulaz [BP] Ulaz [P] Ulaz [SP] Izlaz [BP] Izlaz[P] Izlaz [SP]
Servis i prodaja STYLUS S.P. 142.50 24.23 166.73 2926.64 497.53 3424.17

Need to be multiple ROW

select x1.poslovni_partner as 'Poslovni partner',
CONVERT(decimal(16,2),(x1.Nabavka-(x1.Nabavka*14.5299/100))) AS 'Ulaz [BP]',
CONVERT(decimal(16,2),x1.Nabavka*14.5299/100) AS 'Ulaz [P]',
CONVERT(decimal(16,2),Nabavka) AS 'Ulaz [SP]',
CONVERT(decimal(16,2),Izdavanje-(Izdavanje*14.5299/100)) AS 'Izlaz [BP]',
CONVERT(decimal(16,2),Izdavanje*14.5299/100) AS 'Izlaz[P]',
CONVERT(decimal(16,2),Izdavanje) AS 'Izlaz [SP]'
from(select poslovni_partner, SUM(iznos_sa_porezom) as Nabavka
from(select poslovni_partner, iznos_sa_porezom from mp_ulazni_racun_roba_lista union all
select poslovni_partner, iznos_sa_porezom from mp_ulazni_racun_ostalo_lista union all
select poslovni_partner, iznos_sa_porezom from mp_ulazni_racun_repromaterijal_lista) a
group by poslovni_partner) x1
LEFT join(select kupac, SUM(iznos_sa_pdv) as Izdavanje
from(select kupac, iznos_sa_pdv from mp_racun_lista union all
select kupac, iznos_sa_pdv from mp_faktura_lista union all
select kupac as poslovni_partner, iznos_sa_pdv as iznos_sa_pdv from vp_faktura_lista) b
group by kupac) x2
on x1.poslovni_partner = x2.kupac

Goran Bibic 10-Nov-19 12:28pm

Need for all partners x1.poslovni_partner
This code result is just 1 or need to be issnul to update other

MadMyche 10-Nov-19 19:08pm

I know you need all of it; what you need to do is to debug each of the sub-queries separately, and then join one at a time and recheck.