CODEWITHSUNDEEP
sqlpostgresql
saintlyzero
saintlyzero

Reputation: 1852

Arithmetic Operations on Aggregate function

I have a table foo

  x   |  y  | value 
------+-----+-------
  274 | 617 |     1
   91 | 374 |    10
  996 | 716 |    40
  121 | 442 |     5

I want to add a computed column cal as (value - min(value))/(max(value) - min(value))

Output

  x   |  y  | value | cal
------+-----+-------+-----
  274 | 617 |     1 | 0
   91 | 374 |    10 | 0.23
  996 | 716 |    40 | 1
  121 | 442 |     5 | 0.10

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can use window functions:

select x, y, value, (value - min(value) over ()) / (max(value) over () - min(value) over ()) from t;

If value is an integer, you need to be careful about integer arithmetic:

select x, y, value, (value - min(value) over ()) * 1.0 / (max(value) over () - min(value) over ()) from t;

Upvotes: 1

Related Questions