PostgreSQL: Can't reference column from nested select

Question

I am trying to write a query that includes a nested SELECT. However, Postgres throws this error when I try to run my code: ERROR: column bo_wins.opportunity_id does not exist. Here is the query:

SELECT b.owner_id AS "Owner ID",
       COUNT(DISTINCT bo.id) AS "Number of Opportunities",
       COALESCE(bo_wins.count, 0) AS "Number of Wins"
FROM bookings b
INNER JOIN booking_opportunities bo ON bo.id = b.opportunity_id
LEFT JOIN (
     SELECT b.owner_id AS "Owner ID", COUNT(DISTINCT bo.id) AS "count"
     FROM bookings b 
     INNER JOIN booking_opportunities bo ON bo.id = b.opportunity_id 
     WHERE bo.state IN ('won') AND TO_CHAR(bo.created_at, 'yyyy-mm') = '2016-06' 
     GROUP BY b.owner_id
     ) AS bo_wins ON bo_wins.opportunity_id = b.opportunity_id
WHERE TO_CHAR(bo.created_at, 'yyyy-mm') = '2016-06'
GROUP BY bo_wins.count, b.owner_id;

Hope somebody is able to help me out. The reason I need to do a subquery is because of how our database is structured (I know! Not very efficient, huh?)

EDIT: I aliased my COUNT in the subquery. It still throws me the same error.

Answer 1

Updated Answer

I think what you are trying to do is better represented this way:

SELECT
    b.owner_id AS owner_id,
    COUNT(DISTINCT bo.id) AS opportunities,
    COUNT(DISTINCT CASE WHEN bo.state = 'won' THEN bo.id END) AS wins
FROM
    bookings b 
INNER JOIN
    booking_opportunities bo
    ON (bo.id = b.opportunity_id)
WHERE
    TO_CHAR(bo.created_at, 'yyyy-mm') = '2016-06' 
GROUP BY
    b.owner_id;

This gives you the number of opportunities and number of wins for each owner_id.

---

Initial Answer

You need to give your calculation an alias AND you need to include opportunity_id in that same select statement.

COUNT(DISTINCT bo.id) AS "count"

Also, in your GROUP BY you need to change bo.wins.count to bo_wins.count

Answer 2

Yo should add an alias to COUNT(DISTINCT bo.id)