If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL Aggregate AVG function and GROUP BY Clause
I have a query that goes like this:
SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production]; I assume that this is going to add all the fields in the CurrentSteamflow column and average them....correct? My next query goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production] GROUP BY [Boiler steam production].ReadingDate; I expect this last query to average the CurrentSteamFlow column fields by date ...... is this a correct assumption? Right now I have 3 days worth of readings and this last query does group them by ReadingDate, but if I add the 3 results up and divide by 3 it does not equal the result of the first query... Iam missing something here...any help would be greatly appreciated. Thanks |
#2
|
|||
|
|||
Note that it seems from the alias (Average_lbs/hour) that these are hourly
readings. The first query is going to divide the total by 72 (3 days * 24 hours). The second is going to give you an average for each of the days. You are then comparing the average of the averages to the first query. They are not the same thing. Are the numbers drastically different where you think this may be an SQL problem? Or is this a difference that can be accounted for by rounding errors? Don't forget- floating point math is not exact. -- Chaim "ktm400" wrote in message ... I have a query that goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production]; I assume that this is going to add all the fields in the CurrentSteamflow column and average them....correct? My next query goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production] GROUP BY [Boiler steam production].ReadingDate; I expect this last query to average the CurrentSteamFlow column fields by date ...... is this a correct assumption? Right now I have 3 days worth of readings and this last query does group them by ReadingDate, but if I add the 3 results up and divide by 3 it does not equal the result of the first query... Iam missing something here...any help would be greatly appreciated. Thanks |
#3
|
|||
|
|||
Thanks for the reply Chaim.
The readings are taken every 15 minutes .... The numbers are not drastically different and after going back and doing an ave of each individual day, I realise what you are saying is true. Thanks! "Chaim" wrote: Note that it seems from the alias (Average_lbs/hour) that these are hourly readings. The first query is going to divide the total by 72 (3 days * 24 hours). The second is going to give you an average for each of the days. You are then comparing the average of the averages to the first query. They are not the same thing. Are the numbers drastically different where you think this may be an SQL problem? Or is this a difference that can be accounted for by rounding errors? Don't forget- floating point math is not exact. -- Chaim "ktm400" wrote in message ... I have a query that goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production]; I assume that this is going to add all the fields in the CurrentSteamflow column and average them....correct? My next query goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production] GROUP BY [Boiler steam production].ReadingDate; I expect this last query to average the CurrentSteamFlow column fields by date ...... is this a correct assumption? Right now I have 3 days worth of readings and this last query does group them by ReadingDate, but if I add the 3 results up and divide by 3 it does not equal the result of the first query... Iam missing something here...any help would be greatly appreciated. Thanks |
#4
|
|||
|
|||
And don't cross post! People will see questions in one place. If you cross
post: 1. You don't know where the answers will be posted. You have to look in different places. 2. People will answer in one newsgroup only to find that the question was already answered elsewhere. You have wasted their time, since you have the answer and probably won't come back to read anything else posted to the thread. -- Chaim "ktm400" wrote in message ... I have a query that goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production]; I assume that this is going to add all the fields in the CurrentSteamflow column and average them....correct? My next query goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production] GROUP BY [Boiler steam production].ReadingDate; I expect this last query to average the CurrentSteamFlow column fields by date ...... is this a correct assumption? Right now I have 3 days worth of readings and this last query does group them by ReadingDate, but if I add the 3 results up and divide by 3 it does not equal the result of the first query... Iam missing something here...any help would be greatly appreciated. Thanks |
#5
|
|||
|
|||
sorry - new user, I thought I had posted the original post in the wrong forum
thus this cross post "Chaim" wrote: And don't cross post! People will see questions in one place. If you cross post: 1. You don't know where the answers will be posted. You have to look in different places. 2. People will answer in one newsgroup only to find that the question was already answered elsewhere. You have wasted their time, since you have the answer and probably won't come back to read anything else posted to the thread. -- Chaim "ktm400" wrote in message ... I have a query that goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production]; I assume that this is going to add all the fields in the CurrentSteamflow column and average them....correct? My next query goes like this: SELECT Avg([Boiler steam production]![CurrentSteamFlow]) AS [Average_lbs/hour] FROM [Boiler steam production] GROUP BY [Boiler steam production].ReadingDate; I expect this last query to average the CurrentSteamFlow column fields by date ...... is this a correct assumption? Right now I have 3 days worth of readings and this last query does group them by ReadingDate, but if I add the 3 results up and divide by 3 it does not equal the result of the first query... Iam missing something here...any help would be greatly appreciated. Thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL Aggregate AVG function and GROUP BY Clause | ktm400 | General Discussion | 3 | September 17th, 2005 12:41 AM |
Attaching Code | DS | General Discussion | 2 | August 22nd, 2005 11:21 PM |
Sql aggregate function | SillySally | Running & Setting Up Queries | 5 | March 25th, 2005 10:21 PM |
Total hours from Table into Report | HeatherLou1974 | General Discussion | 3 | March 7th, 2005 08:04 PM |
Function isn't available in expressions in query expression | Reiner Harmgardt | General Discussion | 4 | July 21st, 2004 09:30 AM |