Solved
Need a hand
Posted on 2003-10-21
I have the follwing stored procedure
-- Setup a results table to entry the summary data into
declare @delcoavg float
declare @palmdaleavg float
declare @langdaleavg float
declare @results table (
locname varchar(30),
locid char(4),
rydercost float,
rydergallons float,
comdatacost float,
comdatagallons float,
bulkcost float,
bulkgallons float)
insert into @results(
locname,
locid)
select locname, locid from locations
update @results set rydercost = (
Select Sum(case when Vendorflag ='R' then fueltotal else Null end) as Rydercost from fuelinfo gf where r.locid = gf.locationcode )
from @results r
update @results set comdatacost = (Select Sum(case when Vendorflag ='C' then fueltotal else Null end) as Rydercost from fuelinfo gf where r.locid = gf.locationcode )
from @results r
update @results set rydergallons = (Select Sum(case when Vendorflag ='R' then fuelqty else Null end) as Rydercost from fuelinfo gf where r.locid = gf.locationcode )
from @results r
update @results set comdatagallons = (Select Sum(case when Vendorflag ='C' then fuelqty else Null end) as Rydercost from fuelinfo gf where r.locid = gf.locationcode )
from @results r
update @results set bulkgallons = (Select Sum(case when Vendorflag ='D' or vendorflag = 'P' then fuelqty else Null end) as Rydercost from fuelinfo gf where r.locid = gf.locationcode )
from @results r
declare @getavg table (
vendorflag varchar(2),
fuelavg float
)
insert into @getavg
(vendorflag, fuelavg)
select vendorflag, avg(cost) from fuelinvoice
GROUP BY ALL vendorflag
update @results set bulkcost = (Select Sum(case when Vendorflag ='D' then (select fuelavg from @getavg where vendorflag = 'D')* fuelqty else Null end) as bulkcost from fuelinfo gf where r.locid = gf.locationcode )
from @results r
select * from @results
All of it works fine except for the last update. I get an error Server: Msg 130, Level 15, State 1, Line 55
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
What I need to do is come up with the cost of bulk fuel depending on which vendor it was purchased from. I need to take the avg per gallon cost and multiply this by the number of gallons at the location.
Any suggestions?
Thanks,
Justin