By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH wage_table(worker_id, hourly_rate) AS (
VALUES
(1, 20),
(2, Null),
(3, 30),
(4, 40)
)
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;
| worker_id | hourly_rate | mean_hourly_rate |
|---|---|---|
| 1 | 20 | 30 |
| 2 | null | 30 |
| 3 | 30 | 30 |
| 4 | 40 | 30 |