SQL: how to use column name from an outer clause in an inner clause?

Question

How can I use the value from an outer clause inside an inner clause using nested SELECT statements? Eg:

SELECT cost AS c, quantity, (SELECT COUNT(1) FROM accounts WHERE cost = c) 
FROM accounts

Can c be referenced in the inner SELECT clause as attempted above?

Answer 1

Alias the outer table (eg. FROM accounts AS a). Then you can simply do a.cost in the inner subquery.

EDIT. That being said, there's a better way to write this query without a sub-query for each row:

SELECT a.cost, a.quantity, COUNT(b.id) AS count
FROM accounts AS a LEFT JOIN accounts AS b ON b.cost = a.cost