Question 254528 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – subquery with population and percentages

I need some help writing a subquery. I have columns where I need to take a percentage of a population but based on some other column.

state pop column_a
nyc 10 1
ny 15 1
nyc 20 0
ny 25 0

I need to sum the populations by column_a equals 1 over the whole population based by state.

something like below is the desired outcome.

state %
nyc 50.0
ny 37.5

I have tried window functions, subqueries (not my strength), I am not sure how to craft subquery.

-- numerator

select
    state,
    sum(population)
from table
where column_a = 1
group by state
order by state;

-- denominator

select 
    state,
    sum(population)
from table 
group by state
order by state;

This is as far I get, I am not sure how to combine these two queries to yield the table above.

Tags:

2

Answers


  1. 0 
    Select n.state,
    n.n /d.d
From (select
    state,
    sum(population) n
from table
where column_a = 1
group by state ) n
Join (select 
    state,
    sum(population) d
from table 
group by state) d on n.state = d.state
order by n.state;

    This will work correctly where all states have values for both numerator and denominator.

    Login or Signup to reply.
  2. 0

    In Postgresql you can filter element to sum with FILTER parameter. This way the query comes very simple.

    SELECT
  state,
  sum(pop) FILTER (WHERE c=1) / sum(pop)::float AS avg
FROM data
GROUP BY state
ORDER BY state

    This results:

    state avg
    ny 0.375
    nyc 0.33333
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search