CODEWITHSUNDEEP
postgresqlarea
Lutz
Lutz

Reputation: 223

PostgreSQL calculate area proportion based on two variables

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?

Upvotes: 0

Views: 160

Answers (2)

clamp
clamp

Reputation: 3262

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)

Upvotes: 1

bobflux
bobflux

Reputation: 11591

This does a simple group by soil_type to get the sum(area) per soil_type, that is put in a subquery, and the sum of that is calculated to get the total area:

select soil_type, area, area / sum(area) over () as proportion 
from (select soil_type, sum(area) area from example group by soil_type)x;

 soil_type | area  |     proportion
-----------+-------+---------------------
         3 | 40.95 |  0.2823164426059979
         4 |  43.5 | 0.29989658738366076
         6 |  60.6 | 0.41778697001034126

You could also do:

with x as (select soil_type, sum(area) area from example group by soil_type)
select soil_type, area, area / (SELECT sum(area) FROM x) as proportion from x;

Upvotes: 0

Related Questions