SQL Query value divided by the max(value)

Question

Hi,

I'm ashamed of myself 'cause i can't do this query properly... I have a table like this

  nom  | code_geo | valeur |       indice
-------+----------+--------+--------------------
 AISNE | 02       |  81573 | 0.05
 SOMME | 80       |  79520 | 0.03
 OISE  | 60       |  70004 | 0.09

what i need to do is divide each "indice" by the max(indice). That is:

  nom  | code_geo | valeur |       indice
-------+----------+--------+--------------------
 AISNE | 02       |  81573 | 0.05 / 0.09
 SOMME | 80       |  79520 | 0.03 / 0.09
 OISE  | 60       |  70004 | 0.09 / 0.09

my first guess is:

SELECT nom,code_geo,valeur,indice/(SELECT max(indice) FROM blablabla) FROM blablabla;

my problem is that "blablabla" is actually a function with 6 parameter and i don't want to repeat the FROM clause on the subquery...

Is there another (better?) way to do this? or should i look the other way

Thx in advance

Answer 1

You solution looks fine. Since the subquery is not correlated to the outer query, the DBMS should evaluate that subquery only once.

Answer 2

I believe Postgresql allows CTE.

Select * into yourtable from blablabla


WITH T2(MaxNumber) as
(
select MAX(indice) as MaxNumber from yourtable
)
update t1
set t1.indice=t1.indice / T2.MaxNumber
from yourtable t1, T2