|
This is similar to a recent question,
Number of rows in two tables (24 May, 2007).
The difference here is that the counts have to be counts of
related rows. You were quite right to use
correlated subqueries to achieve the counts.
However, your GROUP BY is both wrong (because it does not contain
the non-aggregate city column that's in the SELECT clause) and
unnecessary. Here's what your query should look like:
select T1.city
, ( select count(*)
from T2
where id = T1.id ) as totbooks
, ( select count(*)
from T3
where id = T1.id
and descr like '%newspaper%' )
as totnewspaper
from T1
Notice that there is no GROUP BY. The subqueries
effectively achieve the "grouping" and each returns a single
value to the outer query.
|
