Problem solve Get help with specific problems with your technologies, process and projects.

SQL counts for separate related tables

I need statistics with count operation for more than two tables.

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 
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.

This was last published in June 2007

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.