I need statistics with count operation for more than two tables:
SELECT T1.city, (select count(*) from T2 where T1.id=T2.id ) as totbooks , (select count(*) from T3 where T1.id=T3.id and descr like '%newspaper%' ) as totnewspaper FROM T1 group by T1.description
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.
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.