Counting Subqueries

Question

My last quest was too vague, so I will revise it. I know the following isn't correct SQL syntax, however you should be able to get the gist of the query I'm trying to perform.

select id, 
       title, 
       count(select x 
              from otherTable 
             where otherTable.id = thisTable.id) as new_row 
 from thisTable

I hope this is a bit better explained.

Answer 1

select tt.id, tt.title, count(ot.id) as count
from thisTable tt
inner join otherTable ot on ot.id = tt.id
group by tt.id, tt.title

Answer 2

Another solution. If you want to know the row count, and not the number of distinct x values, then use count(*) instead of count(distinct x).

select id, title,
    (select count(distinct x) from otherTable
    where otherTable.id = thisTable.id) as new_row 
from thisTable