I have a table, and I wish to add a column which is the average of all lines (not by group).
For example, the table is like:
name num
a 1
b 2
I wish to get:
name num avg_num
a 1 1.5
b 2 1.5
I tried this:
select name, num, avg(num)
from table
group by name, num
However, it returns
name num avg_num
a 1 1
b 2 2
So how can I achieve my goal?
You can specify an empty window (which covers the whole table)
select name, num, avg(num) over() as avg_num from table;