Jen McCown explains correlated subqueries by way of an error message:
What the what?? I literally JUST ran a query exactly like this, but without the join. I haven’t mixed aggregate and non-aggregate columns in the query without a GROUP BY…the only aggregate is in the subquery, and it’s all by its little lonesome!
It’s funny what one little letter can do to you.