Skip to content
Advertisement

How to get the average from computed columns in Postgresql?

I have a query:

SELECT sum(column_1),
   sum(...) as sum_1,
   sum(...) as sum_2,
   sum(...) as sum_3
FROM table_1

How to get an average data from sum_1, sum_2, sum_3? If I write a query in the next way:

SELECT sum(column_1),
   sum(...) as sum_1,
   sum(...) as sum_2,
   sum(...) as sum_3,
   avg(sum_1, sum_2, sum_3)
FROM table_1

I got an Error:

[42703] ERROR: column “sum_1” does not exist

Advertisement

Answer

You seem to want to compute the average of the three columns on the same row. If so, you don’t need avg() (that is an aggregate function, that operates across rows). Instead, you can use regular artihmetics:

SELECT *, (sum_1 + sum_2 + sum_3) / 3 as sum_average
FROM (
    SELECT sum(column_1), sum(...) as sum_1, sum(...) as sum_2,sum(...) as sum_3
    FROM table_1
) t

The subquery is not striclty necessary. You could also repeat the sum()s, like:

SELECT sum(column_1), sum(...) as sum_1, sum(...) as sum_2,sum(...) as sum_3,
    (sum(...) + sum(...) + sum(...)) / 3 as sum_average
FROM table_1
5 People found this is helpful
Advertisement