Update column with average calculation

Question

Original atributes in table boat(bID, name, legth, colour)

Then i create a new atribute called relLength ant i need to to write sentences, where I will update my column (which set to 0 nowstrong text) with this formula:

relLength = Length/AVERAGE length.

I have this atributes in this table called boat: bID, name, length, colour, relLength (this was created)

This Is what I wrote, but is not working

UPDATE boat
SET relLength = length/  (SELECT AVG(length) FROM boat)

(the average is 38.75)

The other exercises is about to create view. I need to take original table (without relLengt inside) and create view where i create this (relLength) atribute and fill with previous formula only in view table and not into table.

Thanks for your help. :)

Answer 1

Try the following:

update boat b, (select avg(length) avg_len from boat) v
set
  b.rellength = v.avg_len

Answer 2

Thanks a lot.

Almost what i've been looking for. Just one minor correction (length/v.avg_len in the last line)

this is what works now for me:

update boat b, (select avg(length) avg_len from boat) v
set
  b.rellength = length / v.avg_len

Can anyone create view? I need to create view from original table boat(bID, name, legth, colour) and create column rellength (like i did this with alter table...) and calculete like it is written above (atribute rellength doesn't exist in original tabel, only in view, where a have this atributes: bID, name, legth, colour, rellength!)

Thanks again :))

Answer 3

I did find the solution for view.

CREATE OR REPLACE VIEW newBoat
AS SELECT b.bid, b.name, b.length, b.color, b.length/(
                            select avg(length) 
                            from boat b) as relLengt
FROM boat b;