sql server - SQL Incorrect total while combining equations -

for report creating need take 2 separate expressions , combine them 1 solution.

the first expression need total sales calculated by

sum([dct].[quantity_stk] *[icp].[unitcost]) [total sales] 

the second expression need total costwhich calculated by

 max(cast ((@purchasecost + @prod_costlbs) * @inputweight decimal (18,2))) [cost] 

both of statements work because used other columns in query , display properly.

in order find out total profit tried combine 2 using sum, avg, max , trying make expression results in error. believe code below has right idea, missing important parts. perhaps over() statement:

cast (([dct].[quantity_stk] *[icp].[unitcost]) - ((@purchasecost + @prod_costlbs) * @inputweight) decimal(18,2)) [profit] 

the problem have is calculating profit each row want total profit. make aggregate function believe. or there way store other 2 expressions variables , use those?

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]    , max(cast ((@purchasecost + @prod_costlbs) * @inputweight decimal (18,2))) [cost]    , max(cast (([dct].[quantity_stk] *[icp].[unitcost]) - ((@purchasecost + @prod_costlbs) * @inputweight) decimal(18,2))) [profit]  (((( 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))  , ((1=1)  , [arc].customerkey in (124) )   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].customerkey  order       cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40))     , cast (@inputweight - sum(sum([ic_productlots].[originalquantity_stk])) on () decimal(18,2))     , max(cast ((@purchasecost + @prod_costlbs) * @inputweight decimal (18,2)))     , max(cast (([dct].[quantity_stk] *[icp].[unitcost]) - ((@purchasecost + @prod_costlbs) * @inputweight) decimal(18,2))) 

temp table

so create expression similar to

select sum([dct].[quantity_stk] *[icp].[unitcost]) total sales  #temptable  (((( 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) group unitcost 

declare variables:

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; 

put raw data temp table this:

    select           cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) [supplier]        , [pc].productcode        , [pc].description1        , ic_productlots.originalquantity_alt [boxes]        , ic_productlots.unitofmeasure_alt        , ic_productlots.originalquantity_stk [weight]        , ic_productlots.unitofmeasure_stk        , [icp].unitcost [unit cost]        , [dct].[quantity_stk] * [icp].[unitcost] [total sales]        , ([ic_productlots].[originalquantity_stk] / [ic_productlots].[originalquantity_alt]) [avg. box weight] --not sure 1        , [ic_productlots].[originalquantity_stk] / @inputweight [yield]        , cast([ic_productlots].[originalquantity_stk] decimal(18,2)) [shrink]        , cast ((@purchasecost + @prod_costlbs) * @inputweight decimal (18,2)) [cost]        , cast (([dct].[quantity_stk] *[icp].[unitcost]) - ((@purchasecost + @prod_costlbs) * @inputweight) decimal(18,2)) [profit]   #temptable      (((( 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))      , ((1=1)  , [arc].customerkey in (124) )  

then either set aggregates variables used in select statement:

declare @totalsales decimal(19,8) = (select sum([total sales]) #temptable declare @totalprofit decimal(19,8) = (select sum([profit]) #temptable declare @totalcost decimal(19,8) = (select sum([cost]) #temptable 

or

select [fieldnames] [aggregates] #temptable group [any field not included in aggregates]