Need a hand



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
LVL 1
jlazanowskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lauszCommented:
Did you try changing null by 0 and using derived tables like this ...

update @results
set bulkcost = t.bulkcost
FROM
(Select Sum(case when Vendorflag ='D' then
(select fuelavg from @getavg where vendorflag = 'D')* fuelqty else 0 end) as bulkcost
from fuelinfo gf where r.locid = gf.locationcode )
    from @results r ) t(bulkcost)



If  ..
(Select Sum(case when Vendorflag ='D' then
(select fuelavg from @getavg where vendorflag = 'D')* fuelqty else 0 end) as bulkcost
from fuelinfo gf where r.locid = gf.locationcode )
    from @results r )  give you thge correct answer I think it can works.
0
jlazanowskiAuthor Commented:
This dosen't work.

I still get Server: Msg 130, Level 15, State 1, Line 56
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.


0
lauszCommented:
Ok.. I think it doesn't work ... ((select fuelavg from @getavg where vendorflag = 'D')* fuelqty )

Can you post an small example with data and what is your desired result ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
As best I can tell, I think maybe the statement below will do what you want.


UPDATE @results
SET bulkcost = CASE WHEN ga.Vendorflag IS NULL
            THEN NULL
            ELSE fuelAvg * fuelQty END
FROM @results r
LEFT JOIN fuelinfo gf ON r.locid = gf.locationcode
LEFT JOIN @getAvg ga ON Vendorflag = 'D'


since it appears to me that you only want the avg price if the Vendorflag = 'D' (? -- then why compute all the avgs?).
0
jlazanowskiAuthor Commented:
No sorry for the confusion.

I want all of the vendor flag averages. What I need to get is the cost of fuel for all of my locations each location has diffrent fuel vendors (who in turn have diffrent fuel costs) if I take the gallons that the location has pumped and multiply it by the average cost for the vendor I should be pretty close to what the bill is. This is what I am looking for.

Make sense?

Thanks,

Justin
0
Scott PletcherSenior DBACommented:
Btw, I think you can replace the first five update statements with this one statement and gain some significant efficiency:


UPDATE @results SET
      rydercost = gfSums.rydercost,
      comdatacost = gfSums.comdatacost,
      rydergallons = gfSums.rydergallons,
      comdatagallons = gfSums.comdatagallons,
      bulkgallons = gfSums.bulkgallons
FROM @results r
LEFT JOIN (
      SELECT locationcode,
          SUM(case when Vendorflag ='R' then fueltotal else 0 end) AS rydercost,
            SUM(case when Vendorflag ='C' then fueltotal else 0 end) AS comdatacost,
            SUM(case when Vendorflag ='R' then fuelqty else 0 end) AS rydergallons,
            SUM(case when Vendorflag ='C' then fuelqty else 0 end) AS comdatagallons,
            SUM(case when Vendorflag IN ('D', 'P') then fuelqty else 0 end) AS bulkgallons
      FROM fuelinfo gf
      GROUP BY locationcode
) AS gfSums ON r.locid = gfSums.locationcode


Similarly, if you want, you can get rid of the @getavg table and just join to a derived table with the AVG, but that won't be as big a gain and you may have other needs for that table, so it's not as big a potential savings.
0
jlazanowskiAuthor Commented:


Here is the results of select * from @results
Location   locid         Ryder Cost                             R_Gallons  Comdata cost                          Comdata gallons                     bulkcos
Belleview      31        408.52999999999997      284.0      2733.1199999999999      2078.6300000000001      NULL
bulkgallons      
62796.300000000003
Tampa      12        78.120000000000005      55.0      NULL      NULL      NULL      NULL
Mayo      32        7.5200000000000005      5.0      1510.9099999999999      1050.3000000000002      NULL      7672.0
Jacksonville      33        8425.2700000000004      5868.0      2216.3899999999999      1558.1899999999998      NULL      854.0
Blackshear      34        922.11000000000013      645.0      2173.1799999999998      1587.8499999999999      NULL      188.0
Cheifland      35        NULL      NULL      NULL      NULL      NULL      3969.0
Bell      36        NULL      NULL      NULL      NULL      NULL      5028.0
Okeechobee      41        97.640000000000015      67.0      813.49000000000012      530.66000000000008      NULL      40127.0
Avon Park      42        NULL      NULL      302.96999999999997      197.11000000000001      NULL      7835.0
Tallahassee      51        5252.3600000000015      3609.0      491.44      342.0      NULL      10968.0
Valdosta      52        11884.079999999998      8924.0      566.96000000000004      403.79000000000002      NULL      1300.0
West Florida      53        262.94      181.0      7376.0500000000065      4947.1900000000014      NULL      667.0
Montezuma      60        3201.7300000000005      2347.0      19538.819999999992      14794.6      NULL      8483.0000000000018



Here is the results from select * from getavg

vendorflag avgcost
D       1.4599999999999997
P       1.4675000000000002

I am trying to take bulkgallons and multiply if by the specific vendors cost.


0
Scott PletcherSenior DBACommented:
But how do you know which avgcost to multiply by?  There is only one bulkgallons total.  Does the fuelinfo table contain a column that identifies the vendor/vendorflag?
0
jlazanowskiAuthor Commented:
Yes there is a vendor flag in the fuel table. Fuel info is a view but it also contains the vendor flag.


0
Scott PletcherSenior DBACommented:
Ah, I *suspected* that but it wasn't really clear from the code (I think; I'm very tired today so this has not been my best day for noticing things).


UPDATE @results
SET bulkcost = CASE WHEN ga.Vendorflag IS NULL
          THEN NULL
          ELSE fuelAvg * fuelQty END
FROM @results r
LEFT JOIN fuelinfo gf ON r.locid = gf.locationcode
LEFT JOIN @getAvg ga ON gf.Vendorflag = ga.Vendorflag  --**changed
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jlazanowskiAuthor Commented:
Ok this can't be it.

When I run this it updates two sites with the bulk cost but the cost is only .02 per gallon.. I wish it were true but I know better. Also it dosen't update all the enties in the bulk cost column just two and even though there are 13 rows with bulkgallons.


Justin
0
jlazanowskiAuthor Commented:
Ok I got part of it. It should be the Sum(fuelqty) since you are picking it back up from the view. Before it was just coming from the @results table.

The other I am not sure of.

Justin
0
jlazanowskiAuthor Commented:
Sorry mis-spoke.. was thinking about it instead of checking it.

If I do

UPPDATE @results
SET bulkcost = CASE WHEN ga.Vendorflag IS NULL
          THEN NULL
          ELSE fuelAvg * r.bulkgallons END
FROM @results r
LEFT JOIN fuelinfo gf ON r.locid = gf.locationcode
LEFT JOIN @getAvg ga ON gf.Vendorflag = ga.Vendorflag

Then I get the right ammount for the bulkcost, but it still only works on two entries a step in the right direction none the less.
0
jlazanowskiAuthor Commented:
Think I found it.

Changed
LEFT JOIN @getAvg ga ON gf.Vendorflag = ga.Vendorflag
To
RIGHT JOIN @getAvg ga ON gf.Vendorflag = ga.Vendorflag

@getavg only has two records so this would make sense. When I run the query I get the right results (or at leas they look right) but I want to make sure I am not messing anything up.

Thanks,

Justin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.