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