CODEWITHSUNDEEP
sqlpostgresqlsumaverageaggregate-functions
Dave Colland
Dave Colland

Reputation: 37

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

Upvotes: 0

Views: 71

Answers (1)

GMB
GMB

Reputation: 222402

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

Upvotes: 3

Related Questions