Get average per partition and keep all results-CodePudding

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.

CodePudding user response：

Use AVG as an analytic function:

SELECT company, AVG(profit) OVER (PARTITION BY company) AS avg
FROM history
WHERE date_num >= 1617235200 AND date_num <= 1619913600 AND company IN ('A', 'B');