How to create a column in SQL containing using the AVG() function?

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?

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.