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

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

Android soft keyboard reverts to default keyboard on orientation change -

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -