Solved

Need a hand

Posted on 2003-10-21
14
225 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility


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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

10 Experts available now in Live!

Get 1:1 Help Now