CODEWITHSUNDEEP
sqlpostgresql
Galuoises
Galuoises

Reputation: 3293

Subtract aggregate function from each column in SQL

I am trying to combine an aggregate function from the corresponding column for each column of a SQL table. For instance, if I want to subtract the average of each column, I can do it with a subquery for each column as 

SELECT 
       col1 - (SELECT AVG(col1) FROM table), 
       col2 - (SELECT AVG(col2) FROM table),
       col3 - (SELECT AVG(col3) FROM table)
FROM table;

However, if there are many columns in the table this is cumbersome, and requires to write column names one by one. Is there a way to simplify the code, or to make it more clear with aliases?

Upvotes: 1

Views: 1224

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Use window functions:

SELECT col1 - AVG(col1) OVER (),
       col2 - AVG(col2) OVER (),
       col3 - AVG(col3) OVER ()
FROM table;

Upvotes: 2

CarlosSR
CarlosSR

Reputation: 1195

I guess you want to compare each individual with the mean of all the values.

SELECT
t1.uid
t1.col1 - t2.col1avg as col1dev
t1.col2 - t2.col2avg as col2dev
t1.col3 - t2.col3avg as col3dev
FROM
table t1
LEFT JOIN (SELECT
     AVG(col1) as col1avg,
     AVG(col2) as col2avg,
     AVG(col3) as col3avg FROM table) t2 ON ( t1.uid = t2.uid)

Upvotes: 0

Related Questions