Solved

Need a hand

Posted on 2003-10-21
14
226 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
  • 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
 
LVL 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Expert Comment

by:ScottPletcher
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 69

Accepted Solution

by:
ScottPletcher earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now