sql - PostgreSQL: Can't reference column from nested select -

i trying write query includes nested select. however, postgres throws error when try run code: error: column bo_wins.opportunity_id not exist. here query:

select b.owner_id "owner id",        count(distinct bo.id) "number of opportunities",        coalesce(bo_wins.count, 0) "number of wins" bookings b inner join booking_opportunities bo on bo.id = b.opportunity_id left join (      select b.owner_id "owner id", count(distinct bo.id) "count"      bookings b       inner join booking_opportunities bo on bo.id = b.opportunity_id       bo.state in ('won') , to_char(bo.created_at, 'yyyy-mm') = '2016-06'       group b.owner_id      ) bo_wins on bo_wins.opportunity_id = b.opportunity_id to_char(bo.created_at, 'yyyy-mm') = '2016-06' group bo_wins.count, b.owner_id; 

hope able me out. reason need subquery because of how our database structured (i know! not efficient, huh?)

edit: aliased count in subquery. still throws me same error.

updated answer

i think trying better represented way:

select     b.owner_id owner_id,     count(distinct bo.id) opportunities,     count(distinct case when bo.state = 'won' bo.id end) wins     bookings b  inner join     booking_opportunities bo     on (bo.id = b.opportunity_id)     to_char(bo.created_at, 'yyyy-mm') = '2016-06'  group     b.owner_id; 

this gives number of opportunities , number of wins each owner_id.

---

initial answer

you need give calculation alias , need include opportunity_id in same select statement.

count(distinct bo.id) "count"

also, in group by need change bo.wins.count bo_wins.count