I have a table with one column and 46 rows and I want to create another column that contains the average of the first column in all 46 rows e.g. This is the table:
CREATE TABLE table2 SELECT column1 FROM table1
I want to add another column that contains for each row (46 rows) the value of AVG(column1)
How do can it be done?
Advertisement
Answer
You wouldn’t use create table
to add a column. In this case, a query using a window function is sufficient:
SELECT t1.*, AVG(column1) OVER () as avg_column1 FROM table1 t1;
This is Standard SQL and should work in any database.