CODEWITHSUNDEEP
c#sql
user1632377
user1632377

Reputation: 41

Sql Query error in asp.net using C# Application

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

Upvotes: 1

Views: 154

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166336

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

Upvotes: 1

Related Questions