Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need a hand

Posted on 2003-10-21
14
Medium Priority
?
233 Views
Last Modified: 2012-08-14


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
0
Comment
Question by:jlazanowski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
14 Comments
 
LVL 6

Expert Comment

by:lausz
ID: 9593343
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593371
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
 
LVL 6

Expert Comment

by:lausz
ID: 9593423
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9593429
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593449
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9593483
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593486


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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9593501
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593573
Yes there is a vendor flag in the fuel table. Fuel info is a view but it also contains the vendor flag.


0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 9593611
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593647
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593664
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593694
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
 
LVL 1

Author Comment

by:jlazanowski
ID: 9593750
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question