Subquery in select statement cant find derived table?

Question

I am wondering if there is a way to make this work. I am deriving a table "WHERE lie_start='green'" (and a bunch of other conditions which i don't wanna repeat), need to get the number (and several other information) off it. Additionally I need the number of entries with the additional condition lie_finish='holed'. Currently I'm gettin the error: Table mydb.x doesnt exist.

SELECT 
    COUNT(*) AS total, 
    (SELECT COUNT(*) FROM x WHERE lie_finish='holed') as holed
FROM (SELECT * FROM mydb.strokes WHERE lie_start='green') as x

Answer 1

You need to repeat the table name. The table alias is not recognized:

SELECT COUNT(*) AS total, 
       (SELECT COUNT(*) FROM mydb.strokes WHERE lie_finish='holed') as holed
FROM (SELECT * FROM mydb.strokes WHERE lie_start='green') as x;

However, this is much more simply written as:

select count(*) as total, sum(lie_finish = 'holed') as holed
from mydb.strokes s
where lie_start = 'green';