Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have these three tables:


pro_id(Pk)  pro_name   
1           ABC           
2           def           


pid pro_id               
1   1                      
2   1                      
3   1                                                 
4   2                                                 
5   2
6   2  


pro_id  code
1       dc23
1       ef45
2       456F
2       4er

How can I write a query that would give this result?

pro_id    noof pids  noofcodes
1         3          2
2         3          2
share|improve this question

The following would do it. SQL Fiddle

     AS (SELECT pro_id,
                COUNT(pid) AS [noof pids]
         FROM   Table2
         GROUP  BY pro_id),
     AS (SELECT pro_id,
                COUNT(code) noofcodes
         FROM   Table3
         GROUP  BY pro_id)
SELECT T1.pro_id,
       COALESCE([noof pids], 0) AS [noof pids],
       COALESCE(noofcodes, 0)   AS noofcodes
FROM   Table1 T1
       LEFT JOIN T2
         ON T1.pro_id = T2.pro_id
       LEFT JOIN T3
         ON T1.pro_id = T3.pro_id 
share|improve this answer

You can also just use two scalar subqueries:

SELECT pro_id,
      (SELECT COUNT(*) FROM Table2 WHERE pro_id=A.pro_id) [noof pids],
      (SELECT COUNT(*) FROM Table3 WHERE pro_id=A.pro_id) [noofcodes]
  FROM Table1 A;
share|improve this answer
Do we need those ISNULLs? I thought COUNT never returned NULL. – AakashM Apr 4 '13 at 9:58
@AakashM You're absolutely right. It didn't even register with me after SQL Server showed the missing 2nd COMPUTE SCALAR in the query plan, because it optimized away the ISNULL. Now it's becoming clear. – 孔夫子 Apr 4 '13 at 10:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.