At the final hurdle

[GWB]

I have built a query to give me the average of readings taken every 15 minutes by day and year, here is the code:

SELECT     TOP 100 PERCENT AVE(CurrentSteamFlow) AS AverageOfSteamPerHour, YEAR(ReadingDate) AS [Year], DAY(ReadingDate) AS [Day], 
                      MONTH(ReadingDate) AS [Month], { fn HOUR(ReadingTime) } AS [Hour]
FROM         dbo.PWL_Boiler_Steam_Production
GROUP BY YEAR(ReadingDate), DAY(ReadingDate), MONTH(ReadingDate), { fn HOUR(ReadingTime) }
ORDER BY YEAR(ReadingDate) DESC, MONTH(ReadingDate) DESC, DAY(ReadingDate) DESC, { fn HOUR(ReadingTime) } DESC

What I need to do now is add all these averages by year. Its eluded me so far...
Thanks for any help!

[Rawb]

I'm not sure I understand the question: Are you wanting to simply add the yearly averages together, or are you average the averages?

If you want to sum the yearly averages to get a "total of yearly averages" value, then your best bet is to save the above Query in your db. Then make the following Query:

SELECT SUM(AverageOfSteamPerHour) AS SumOfAverageSteamPerYear FROM MySavedQuery;

If you have to use VBA Code, you can use the following to sum the values:

  Dim nbrSumOfAvg As Double

  nbrSumOfAvg = 0

  Do While Not rstMyRecordset.EOF
    nbrSumOfAvg = nbrSumOfAvg + rstMyRecordSet("AverageOfSteamPerHour")

    rstMyRecordSet.MoveNext
  Loop

If you're looking to get the lifetime average though, just remove your groupings. That way the Query just returns a single record with your lifetime average.

[GWB]

I'm not sure I understand the question: Are you wanting to simply add the yearly averages together, or are you average the averages?

If you want to sum the yearly averages to get a "total of yearly averages" value, then your best bet is to save the above Query in your db. Then make the following Query:

SELECT SUM(AverageOfSteamPerHour) AS SumOfAverageSteamPerYear FROM MySavedQuery;

If you have to use VBA Code, you can use the following to sum the values:

  Dim nbrSumOfAvg As Double
 
  nbrSumOfAvg = 0
 
  Do While Not rstMyRecordset.EOF
    nbrSumOfAvg = nbrSumOfAvg + rstMyRecordSet("AverageOfSteamPerHour")
 
    rstMyRecordSet.MoveNext
  Loop

If you're looking to get the lifetime average though, just remove your groupings. That way the Query just returns a single record with your lifetime average.

Thanks for that.