Presto query: add the average value as a new column

Question

I have the following wage_table:

worker_id, hourly_rate
  1      , 20
  2      , Null
  3      , 30
  4      , 40

I want to add an additional column mean_hourly_rate like:

worker_id, hourly_rate, mean_hourly_rate
  1      , 20         ,  30
  2      , Null       ,  30
  3      , 30         ,  30
  4      , 40         ,  30

I have the following code, but got error 'column worker_id not in GROUP BY clause'

SELECT
    *,
    AVG(hourly_rate) AS mean_hourly_rate
FROM wage_table

I am wondering what's the correct way of doing it? Thanks!

Answer 1

You can use a window function: avg(hourly_rate) OVER ()

For example:

WITH data(worker_id, hourly_rate) AS (
    VALUES
        (1, 20),
        (2, Null),
        (3, 30),
        (4, 40)
)
SELECT 
    worker_id, 
    hourly_rate, 
    avg(hourly_rate) OVER () AS mean_hourly_rate
FROM data

produces:

 worker_id | hourly_rate | mean_hourly_rate
-----------+-------------+------------------
         1 |          20 |             30.0
         2 |        NULL |             30.0
         3 |          30 |             30.0
         4 |          40 |             30.0
(4 rows)

Answer 2

You may also use cross join

 SELECT
    T1.*, T2. mean_hourly_rate
 FROM wage_table T1
 CROSS JOIN (select AVG(hourly_rate) AS mean_hourly_rate from wage_table
Where hourly_rate is NOT NULL) T2;

Here is a db fiddle link - https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=8c819f2e1e040c74c0f0514e271a4c84