CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
mrcool4
mrcool4

Reputation: 93

avg of multiple columns

I would like to find the avg of multiple columns instead of rows. At present, I transposed the table but that's impacting the performance as my table is very big and by transposing 30 columns the number of rows increased * 29 times.

colum1 measure1 measure2 measure3 avg abc 100 200 300 200 def 50 60 70 60

I am not going to use all the 30 columns at a time for average and it depends on my parameters in the front end.

I would like to know if there any other solutions to achieve the desired result other than transpose. In Redshift, I am doing a union of table 29 times to transpose columns to rows.

Your advises would be highly appreciated.

Thanks, mc

Upvotes: 0

Views: 1540

Answers (1)

Jacek Trociński
Jacek Trociński

Reputation: 920

Try something like this (Oracle query):

WITH input_data AS (
  SELECT 100 AS measure1, 200 AS measure2 FROM DUAL
  UNION ALL
  SELECT 1000 AS measure1, 2000 AS measure2 FROM DUAL
)
SELECT (a.measure1 + a.measure2) / 2 AS measure_avg FROM input_data a

Output:

MEASURE_AVG
150
1500

Upvotes: 2

Related Questions