Calculating ratio to maximum in Postgres

user_5798365 0
Viewed 60

I have this table in Postgres:

name - varchar, weight and height - int4. I need to compute the ration of weight to maximum weight and height to the maximum height for each record.

So far I have only this:

select people.name, people.weight, people.height, people.weight/max(people.weight) 
as weight_ratio, people.height/max(people.height) from people

but I get this error: column "people.name" must appear in the GROUP BY clause or be used in an aggregate function

How do I fix the issue?

max postgresql
1 Answers

Use MAX() window function:

SELECT name, weight, height, 
       weight / MAX(weight) OVER() weight_ratio, 
       height / MAX(height) OVER() height_ratio 
FROM people

If weight and height are integers you should also multiply by 1.0 before the division, because Postgresql performs integer division between integers and will truncate any decimal part:

SELECT name, weight, height, 
       1.0 * weight / MAX(weight) OVER() weight_ratio, 
       1.0 * height / MAX(height) OVER() height_ratio 
FROM people
user_104988280
Related