Re: How to find avg() of sum()?

semi-ambivalent wrote on 16.04.2010 19:57:
> I have some data fields that I have summed, grouped by a date field.
> The sums are different. How can I then calculate the average value for
> those sums? Everything I've tried errors out with something along the
> lines of using agregates where I can't, or for using multiple values
> where that is not allowed. I'm sure this can be done in one query,
> without temp tables, but I don't know it and haven't found it yet in
> the docs.

Assuming your sum() statement looks like:

SELECT one_field, sum(other_field)
FROM the_table
GROUP BY one_field;

You can get the average of the sums using:

SELECT avg(the_sum)
FROM (
SELECT one_field, sum(other_field) as the_sum
FROM the_table
GROUP BY one_field
) t

Thomas