aggregate columns across rows

Question

with a table like below, need to compute the mean and median for val1 to val4 for each row as val_mean and val_median respectively.

+----+------+------+------+------+------+
| id | loc  | val1 | val2 | val3 | val4 |
+----+------+------+------+------+------+
|  1 | loc1 |   10 | 190  | null |   20 |
|  2 | loc2 |   20 | null | 10   |   10 |
+----+------+------+------+------+------+

In general how do we go about handling aggregation across columns ?

one approach could be to split val1 to val4 into 4 different rows then aggregate them by id, loc to find the mean/median. So the above table would need to be transformed into below format first - how might I go abt this ?

+----+------+--------+
| id | loc  |  val   |
+----+------+--------+
|  1 | loc1 |   10   |
|  1 | loc1 |   190  |
|  1 | loc1 |   null |
|  1 | loc1 |   20   |
|  2 | loc2 |   20   |
|  2 | loc2 |   null |
|  2 | loc2 |   10   |
|  2 | loc2 |   10   |
+----+------+--------+

Possible duplicate of: http://stackoverflow.com/questions/12067656/how-do-i-get-min-median-and-max-from-my-query-in-postgresql — Tim Biegeleisen, Mar 20 '17 at 03:03
How do you want `NULL` values to be handled when computing the median? — Tim Biegeleisen, Mar 20 '17 at 03:04
Will you always have 4 value columns, or could there be more later on? — Tim Biegeleisen, Mar 20 '17 at 03:08
You're going to need a user defined function for the median. Check the duplicate link. — Tim Biegeleisen, Mar 20 '17 at 03:12
isn't the available function `percentile(0.5 ...)` same as median ? — user3206440, Mar 20 '17 at 03:19
how abt the approach of splitting each of the val1 to val2 into rows of itself with the same id, loc fields and then aggregating across rows by grouping? — user3206440, Mar 20 '17 at 03:42
Yes, this approach might work. But it would be better to change your table design. Postgres' aggregate functions were intended to be applied over rows. — Tim Biegeleisen, Mar 20 '17 at 03:47
I get the data from external sources, no option to change that :( — user3206440, Mar 20 '17 at 03:48

score 1 · Accepted Answer · answered Mar 20 '17 at 10:52

select id, loc, avg(val), percentile_disc(0.5) within group (order by val)
from (
  select id, loc, val1 as val
  from t
  union all
  select id, loc, val2
  from t
  union all
  select id, loc, val3
  from t
  union all
  select id, loc, val4
  from t
) s
group by id, loc
;
 id | loc  |         avg         | percentile_disc 
----+------+---------------------+-----------------
  1 | loc1 | 73.3333333333333333 |              20
  2 | loc2 | 13.3333333333333333 |              10

aggregate columns across rows

1 Answers1