sql - Error while using OVER clause and variables -
i trying use variables preform calculation , display aggregate function. formula calculates total cost of product.
@purchasecost
how item cost per pound. ex. $5.50
@prod_costlbs
how labor cost per pound. ex. $1.00
@inputweight
how many pounds there are. ex. 4,000
of these variables input user. calculate total cost using formula:
cast ((@purchasecost + @prod_costlbs) * @inputweight over() decimal (18,2)) [cost]
however, error:
the same query view cannot show column group designation of 'expression' without aggregate function when column contains group designation of 'group by'
i using microsoft sql server 2005.
full code:
set nocount on; declare @purchasecost decimal(19,8); declare @inputweight decimal(19,8); declare @prod_costlbs decimal(19,8); set @purchasecost = 2.58; set @inputweight = 18100; set @prod_costlbs = .15; select cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) [supplier] , [pc].productcode , [pc].description1 , count(ic_productlots.originalquantity_alt) [boxes] , ic_productlots.unitofmeasure_alt , sum(ic_productlots.originalquantity_stk) [weight] , ic_productlots.unitofmeasure_stk , [icp].unitcost [unit cost] , sum([dct].[quantity_stk] *[icp].[unitcost]) [total sales] , avg(([ic_productlots].[originalquantity_stk] / [ic_productlots].[originalquantity_alt])) [avg. box weight] , sum([ic_productlots].[originalquantity_stk] / @inputweight) [yield] , cast (@inputweight - sum(sum([ic_productlots].[originalquantity_stk])) on () decimal(18,2)) [shrink] , cast ((@purchasecost + @prod_costlbs) * @inputweight decimal (18,2)) [cost] (((( ic_products [pc] inner join dc_transactions [dct] on [pc].productkey = [dct].productkey) inner join ar_customers [arc] on [dct].customerkey = [arc].customerkey) inner join ic_productlots on [dct].lotkey = ic_productlots.lotkey) left outer join ic_productcosts [icp] on icp.productkey=pc.productkey , icp.productcostcode=5) (ic_productlots.productiondate >= { ts '2015-06-24 00:00:00' } , (ic_productlots.productiondate <= { ts '2015-06-24 00:00:00' } or ic_productlots.productiondate null)) , ([arc].customercode = ' 904') group cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) , [pc].productcode , [pc].description1 , ic_productlots.unitofmeasure_alt , ic_productlots.unitofmeasure_stk , [icp].unitcost , ic_productlots.productiondate , [arc].customercode order cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) , cast (@inputweight - sum(sum([ic_productlots].[originalquantity_stk])) on () decimal(18,2))
can post full code? try this
cast ((@purchasecost + @prod_costlbs) * @inputweight decimal (18,2)) [cost]
also instead of
instead of cast (@inputweight - sum(sum([ic_productlots].[originalquantity_stk])) on () decimal(18,2)) [shrink]
use
`cast (@inputweight - sum([ic_productlots].[originalquantity_stk]) decimal(18,2)) [shrink]
Comments
Post a Comment