Groupby year, calculate sum and percentage per year-CodePudding

I have a table with the columns

datefield area

I want to calculate sum of area per year and a percentage column

year   sum    percentage
2022   5  12
2023   10 24
2024   6  15
[null] 20 49

(I have many more years in the table which I want to include)

WITH total as(
select extract(YEAR from "datefield") theyear, sum(area) as totalarea 
from thetable 
group by extract(YEAR from "datefield")
)
select total.theyear, total.totalareal,
        totalarea/(SUM(totalarea) OVER (PARTITION BY theyear))*100
from total

I get correct sum, but all the percentages are 100..

What am I doing wrong?

Some sample data:

2019    7.05
2020    4.77
2020    3.56
2021    1.64
2021    8.37
2021    3.51
2021    1.43
2021    9.94
2022    1.91
2022    5.3

I would like the result

2019    7.05    15
2020    8.33    18
2021    24.89   52
2022    7.21    15

CodePudding user response：

WITH 
total as
(
select extract(YEAR from "datefield") theyear, sum(area) as totalarea,
       SUM(sum(area)) OVER() as SUM_totalarea
from   thetable 
group  by extract(YEAR from "datefield")
)
SELECT theyear, totalarea, 100.0 * totalarea / SUM_totalarea AS PERCENTAGE
FROM   total