0

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
25
Views
5 Months
Discussion Span
Last Post by Shark_1
0

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

0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.