3

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like:

SELECT 2+3+4

How can I calculate the average of 2, 3 and 4 without inserting them to a table first?

I have tried different kinds of variations like:

SELECT 2 AS foo UNION 3 AS foo UNION 4 AS foo
  foo
  integer
1 2
2 3
3 4

but:

SELECT AVG(SELECT 2 AS foo UNION 3 AS foo UNION 4 AS foo)

ERROR: syntax error at or near "select"

I just can't seem to find the right syntax. I'm on PostgreSQL at the moment if it matters.

  • 1
    SELECT AVG(foo) FROM (SELECT 2 as foo UNION SELECT 3 UNION SELECT 4)x – Mihai Sep 18 '14 at 16:38
  • 1
    @Mihai: Unless the task requires to fold duplicate values, UNION is incorrect, strictly speaking. – Erwin Brandstetter Sep 18 '14 at 23:58
5

The best solution depends on where and in what form the values come from.

unnest()

Use the set returning function unnest() with an array in the FROM clause. That's shortest for long lists, but it's a Postgres specific solution.

SELECT avg(n) FROM unnest('{2,3,4}'::int[]) n;

Which is short syntax for:

SELECT avg(n) FROM unnest('{2,3,4}'::int[]) AS n(n);

VALUES

Alternatively, use a VALUES expression as subquery. This is standard SQL.

SELECT avg(n)
FROM  (VALUES (2),(3),(4)) t(n);

UNION ALL

You can do the same with a much more verbose chain of SELECT ... UNION ALL SELECT .... Also standard SQL.

SELECT avg(n)
FROM  (
   SELECT 2 
   UNION ALL
   SELECT 3
   UNION ALL
   SELECT 4
   ) t(n);

Use UNION ALL, not just UNION, which would fold duplicates (which you did not specify!) and is therefore also considerably more expensive.

SQL Fiddle.

Note that all numeric literals in the example are cast to integer automatically (just digits). For other data types you may have to add explicit type casts. avg() returns (per documentation):

numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type

1

For your case, SELECT AVG(foo) FROM (SELECT 2 as foo UNION SELECT 3 UNION SELECT 4) aliasname

like what Mihai said.

Here is the generic syntax:

SELECT AVG (* | [DISTINCT] ALL | column_name) FROM (table_name)

I would suggest learning the function by using columns that have numeric or integer values to average before you go down your route. Also remember to use group by if you mix aggregate and non-aggregate columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.