Member Avatar for JModak

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

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
Edited by JModak
  • 3 Contributors
  • 2 Replies
  • 393 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Santanu.Das
Member Avatar for xrjf

The message is telling that field pr.invno on line 1 should also figure in the Group By clause, or in a function like sum(pr.invno) or count(pr.invno) .

Member Avatar for Santanu.Das

Group By clasue always produce a count or summation of another field. It never shows other feilds, if you tried to so the values of other fields without aggregating them it shows an error message.

If you want to group by with a single field you only can get the aggregated fields. The syntax should be 

select p.prtgst,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 prd.purinvdt ='2018-01-04' GROUP BY  prd.pdcgst

But to get every feild values the systax should be

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,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.