'Get average per partition and keep all results

I have a table like this:

company date_num profit
A        EPOCH   12
B        EPOCH   17
A        EPOCH   7
C        EPOCH   9

I would like to calculate average per company and keep the profit column:

company date_num profit  AVG
A        EPOCH   12      9.5
A        EPOCH   7       9.5
B        EPOCH   17      17
C        EPOCH   9       9

So the average column will have the same number(avg) for all rows of a company, and will calculate a single average for all company values.

I tried:

SELECT company,
AVG(profit) as avg
FROM history
WHERE date_num >= 1617235200 AND date_num <= 1619913600 AND company IN('A','B')
GROUP BY company

Which produce a single line result.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source