我有一个表,并希望按动态生成的列对记录进行分组。如我的查询中所说,它是newcol。在执行此查询时,我收到错误:
选择列表中的“ SalesProductDetails.ProductId”列无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中。
这是我尝试过的查询
select tbl.nwecol,tbl.ProductId,tbl.Products_Name,tbl.Qunatity,tbl.SKUCode
from (
select SPD.ProductID,
PR.SKUCode,
PR.Products_Name,
sum(SPD.Qunatity) as Qunatity,
cast(round(((SPD.BasePrice*SPD.Qunatity)+STD.SalesTaxAmt)/SPD.Qunatity,2) as numeric(36,2)) as nwecol,
SM.Isactive
from SalesProductDetails SPD
join SalesMaster SM
on SPD.SalesId=SM.SalesId
join Sales_TaxDetails STD
on SPD.ProductSalesID=STD.ProductSalesID
join Products Pr
on Pr.ProductID=SPD.ProductId
where SPD.Isactive=1
and SM.Isactive=1
and SPD.ProductId=1
) as tbl
group by tbl.nwecol,tbl.ProductId,tbl.Products_Name,tbl.Qunatity,tbl.SKUCode
我认为您要执行的操作是根据您生成的列(nwecol)计算总和。在这种情况下,您需要将聚合(SUM)移到子查询之外,如下所示:
select tbl.nwecol,tbl.ProductId,tbl.Products_Name,tbl.SKUCode,
SUM(tbl.Qunatity)
from (
select SPD.ProductID,
PR.SKUCode,
PR.Products_Name,
SPD.Qunatity
cast(round(((SPD.BasePrice*SPD.Qunatity)+STD.SalesTaxAmt)/SPD.Qunatity,2) as numeric(36,2)) as nwecol,
SM.Isactive
from SalesProductDetails SPD
join SalesMaster SM
on SPD.SalesId=SM.SalesId
join Sales_TaxDetails STD
on SPD.ProductSalesID=STD.ProductSalesID
join Products Pr
on Pr.ProductID=SPD.ProductId
where SPD.Isactive=1
and SM.Isactive=1
and SPD.ProductId=1
) as tbl
group by tbl.nwecol,tbl.ProductId,tbl.Products_Name,tbl.SKUCode
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句