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