Sql Query error in asp.net using C# Application

Question

i have simply tried to show the details by data grid make use of this query

select 
P.Sitename as Site
,P.Vendorname as Vendor
,P.POno,PS.Itemcode as ItemCode
,sum(PS.Quantity) as Qty
,sum(PS.Basic) as Basic
,Sum(PS.DiscountAmt) as [Disc Amt]
,sum(PS.PFAmt) as [PF Amt]
,sum(PS.EDAmt) as [ED Amt]
,sum(PS.VATCSTAmt) as [VATCST Amt]
,sum(PS.Netamt) as Total
,I.Itemname as Itemname
,I.UOM as UOM  
from tbl_Itemmaster I, tbl_Purchaseorder_sub PS,tbl_Purchaseorder P 
where P.POdate between '8/30/2012' and '8/31/2012' 
and PS.status in (4,5) 
and I.Itemidentify=Ps.ITemcode 
and PS.pono=p.POno 
group by PS.Itemcode,P.Sitename,P.Vendorname,I.Itemname,I.UOM   
order by Site,Vendor,ItemCode

but, i have got the error while executing this, which is,

"Column 'tbl_Purchaseorder.POno' is invalid in the select list because 
it is not contained in either an aggregate function or the GROUP BY clause."

Please can anyone tell me how can i correct it

Adriaan Stander · Accepted Answer · 2012-08-31 06:50:42Z

1

You need to add POno to the GROUP BY Cluase

Something like

select  P.Sitename as Site,
        P.Vendorname as Vendor,
        P.POno,
        PS.Itemcode as ItemCode,
        sum(PS.Quantity) as Qty,
        sum(PS.Basic) as Basic,
        Sum(PS.DiscountAmt) as [Disc Amt],
        sum(PS.PFAmt) as [PF Amt],
        sum(PS.EDAmt) as [ED Amt],
        sum(PS.VATCSTAmt) as [VATCST Amt],
        sum(PS.Netamt) as Total ,
        I.Itemname as Itemname,
        I.UOM as UOM  
from    tbl_Itemmaster I, 
        tbl_Purchaseorder_sub PS,
        tbl_Purchaseorder P 
where   P.POdate between '8/30/2012' 
and     '8/31/2012' 
and     PS.status in (4,5) 
and     I.Itemidentify=Ps.ITemcode 
and     PS.pono=p.POno 
group by    PS.Itemcode,
            P.Sitename,
            P.Vendorname,
            I.Itemname,
            I.UOM,
            P.POno <-- You need to add this to your query
order by    Site,
            Vendor,
            ItemCode

answered Aug 31, 2012 at 6:50

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Divi Over a year ago

Please select this as your answer, if it answered your question

Collectives™ on Stack Overflow

Sql Query error in asp.net using C# Application

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related