Tom> I'm wondering why this didn't already fail at "counts.count",
Because counts.count resolves as count(counts), obviously. That makes the query an aggregation query with implied GROUP BY (), hence the error.
Justin: the problem is nothing to do with the join, but it _is_ to do with the AS alias. For historical compatibility reasons, PostgreSQL tries to treat x.y and y(x) as though they were somewhat equivalent; so you can do (under some conditions) x.function or columnname(table). Needless to say actually _using_ this facility is a very bad idea.
So in this example, if you have a column called "count", then counts.count resolves to that column. But if there's no column called "count", then counts.count is resolved as count(counts) (which works because count() is one of the few functions that can take any parameter type), and since count() is an aggregate function, that forces the query to behave as if there were an implied GROUP BY (), just as doing something like select count(*) from table; does.
