SQL Aggregate AVG function and GROUP BY Clause

K

ktm400

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
 
6

'69 Camaro

Hi.
I assume that this is going to add all the fields in the CurrentSteamflow
column and average them....correct?
Click to expand...

If you agree with how NULL's are treated in this average, then yes.
I expect this last query to average the CurrentSteamFlow column fields by
date ...... is this a correct assumption?
Click to expand...

Again, if you agree with how NULL's are treated in this average, then yes.
but if I add the 3 results up and divide by 3 it does
not equal the result of the first query...
Click to expand...

Whoa! Averaging "averages" does not give meaningful results. If by some
coincidence the numbers are evenly distributed, then the averages will "match
up" to the total average, but not otherwise. Which means one shouldn't rely
on a mere coincidence for math calculations.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


ktm400 said:
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
Click to expand...
 
K

ktm400

Thank you .
I realise my error now!

'69 Camaro said:
Hi.
I assume that this is going to add all the fields in the CurrentSteamflow
column and average them....correct?
Click to expand...

If you agree with how NULL's are treated in this average, then yes.
I expect this last query to average the CurrentSteamFlow column fields by
date ...... is this a correct assumption?
Click to expand...

Again, if you agree with how NULL's are treated in this average, then yes.
but if I add the 3 results up and divide by 3 it does
not equal the result of the first query...
Click to expand...

Whoa! Averaging "averages" does not give meaningful results. If by some
coincidence the numbers are evenly distributed, then the averages will "match
up" to the total average, but not otherwise. Which means one shouldn't rely
on a mere coincidence for math calculations.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


ktm400 said:
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
Click to expand...
Click to expand...
 
J

John Vinson

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?
Click to expand...

Yes. One nitpick: it's preferable to use . rather than ! as a
delimiter in SQL queries; ! is used for form and other object
references.
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?
Click to expand...

Well... almost. It collects the table records in groups, grouping by
ReadingDate; and averages each group's results independently.
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.
Click to expand...

The average of the averages will NOT necessarily equal the grand
average, if there are different numbers of records on each day.

Consider the following sample data:

9/20 1
9/20 2
9/20 3

9/21 3
9/21 3
9/21 3
9/21 3
9/21 3
9/21 3
9/21 3
9/21 3
9/21 3

The average for 9/20 is 2; the average for 9/21 is 3. If you average
those two values you'll get 2.5.

However, if you average all 12 records you'll get 33/12 = 2.75.

This isn't anything wrong with Access or the way it calculates
averages - it's just a mathmatical truism!


John W. Vinson[MVP]
 
Top