PostgreSQL calculate area proportion based on two variables

Question

I am relatively new to PostgreSQL and can´t really figure out a simple problem. Take a simplified table as an example:

create table example(
    objectid integer,
    soil_type integer,
    area double precision);
    values (2,4,23.5),
    (2,3,30.45),
    (2,6,20.1),
    (1,3,10.5),
    (3,6,40.5),
    (3,4,20);

How do I calculate the area share of each soil type in each object id?

Answer 1

You need the area sum for each obectid. - But at the same time keep all the rows. This is done with a window function: sum(area) OVER(PARTITION BY objectid).
- Or, if you need the share of each objectid per soil_type, PARTITION BY soil_type.

SELECT 
objectid, 
soil_type,
area, 
area / sum(area) OVER(PARTITION BY objectid) AS share 
FROM example 
ORDER BY 1,2;

Output:

 objectid | soil_type | area  |        share        
----------+-----------+-------+---------------------
        1 |         3 |  10.5 |                   1
        2 |         3 | 30.45 | 0.41120864280891284
        2 |         4 |  23.5 | 0.31735313977042534
        2 |         6 |  20.1 |  0.2714382174206617
        3 |         4 |    20 |  0.3305785123966942
        3 |         6 |  40.5 |  0.6694214876033058

Or:

SELECT 
objectid, 
soil_type,
area, 
area / sum(area) OVER(PARTITION BY soil_type) AS share 
FROM example 
ORDER BY 1,2;

Output:

 objectid | soil_type | area  |        share        
----------+-----------+-------+---------------------
        1 |         3 |  10.5 |  0.2564102564102564
        2 |         3 | 30.45 |  0.7435897435897435
        2 |         4 |  23.5 |  0.5402298850574713
        2 |         6 |  20.1 |  0.3316831683168317
        3 |         4 |    20 | 0.45977011494252873
        3 |         6 |  40.5 |  0.6683168316831684
(6 rows)