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

Thomas Kellerer <spam_eater@xxxxxxx> · Fri, 16 Apr 2010 23:18:38 +0200

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general