Compute Averages/Stdevs for multiple columns in sql

I have a sql table with a handful of numeric columns, say my_field_1, my_field_2,…,my_field_n. I’d like to be able to view some aggregate statistics for each of these fields in a nice way.

My current solution is code that looks like this:

select
  max(field_name) as field_name
  , avg(field) as average
  , stddev(field) as stddev
  , sum(case when field is null then 1 else 0 end) as null_vals
  , max(field) as max_val
  , min(field) as min_val
from
  (select 'first field' as field_name, my_field_1 as field from my_table) t

union all

...

With one block for each field I care about. This works, but it results in a lot of duplicated code, and it’s annoying when I want to add another summary statistic. I could create a bash script which generates this query instead of writing it by hand, but is there a better way?

Answer

You can use a lateral join:

select field_name as field_name,
       avg(field) as average,
       stddev(field) as stddev,
       sum(case when field is null then 1 else 0 end) as null_vals,
       max(field) as max_val,
       min(field) as min_val
from my_table t cross join lateral
     (values ('my_field_1', my_field1),
             ('my_field_2', my_field2),
             . . .
     ) v(field_name, field)
group by field_name;

As with your query, this assumes that all the field values are of compatible types — and hopefully the same type.