Sql group by error in inner join please help me

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

i have 3 table i inner join with 3 table and add function but now getting error
please help me how to solve the the problem please help me

ERROR :

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

TABLE 1: TBL_pur_inv as pr
purinvid slno pdcgst cgstamt pdsgst sgstamt pdigst igstamt TaxableAmt totaltaxamt

TABLE 2: tbl_party_ldg AS p

partyID prtynm prtgst prtpan

TABLE 3: TBL_pur_invdet AS prd
purinvid purinvdt invno invdt partyIDpur invamt

What I have tried:

VB

select p.prtgst,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst,sum(prd.totaltaxamt)As Rate,sum(prd.TaxableAmt)As Taxable_Amount
from TBL_pur_inv as pr
 INNER JOIN tbl_party_ldg  AS p   ON pr.partyIDpur = p.partyID
 INNER JOIN TBL_pur_invdet  AS prd   ON pr.purinvid = prd.purinvid
 
where purinvdt ='2018-01-04' GROUP BY  prd.pdcgst

Posted 6-Feb-18 3:10am

Jayanta Modak

Updated 6-Feb-18 3:21am

Add a Solution

2 solutions

Solution 2

SQL aggregate functions (like sum) have some rules - of reason...
You can not create a row (a list of columns) where the same raw contains both simple and aggregated values... except if your simple values are used to create groups (breaks) in the query...

The reason in nutshell - An SQL query will return a single row for each item, but aggregate function returns a single value for all the rows in the data...

Posted 6-Feb-18 3:21am

Kornfeld Eliyahu Peter

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)

Maciej Los · Accepted Answer · 2018-02-06T03:19:00

Solution 1

The error message is quite clear... You should change a GROUP BY part to:

SQL

GROUP BY p.prtgst,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst

Posted 6-Feb-18 3:19am

Maciej Los

Updated 6-Feb-18 3:23am

v2

Comments

Jayanta Modak 6-Feb-18 9:45am

thanks sir
it is done
but (prd.pdcgst) pdgst are three type 0.00,2.5,9.00 i want to show only 2.5, and 9.00 how to hide the 0.00 pdgst
and show 5.00 in place of 2.5 and 18.00 in place of 9.00

Jayanta Modak 6-Feb-18 9:57am

but (prd.pdcgst) pdgst are three type 0.00,2.5,9.00 i want to show only 2.5, and 9.00 how to hide the 0.00 pdgst
and show 5.00 in place of 2.5 and 18.00 in place of 9.00

Maciej Los 6-Feb-18 11:16am

Just multiply prd.pdcgst * 2 and add condition at the of statement: WHERE prd.pdcgst>0.00
;)

Jayanta Modak 6-Feb-18 22:51pm

thanks sir
it is done just i want to
thanks a lot

Maciej Los 7-Feb-18 1:49am

You're very welcome.