title: SQL Nested Select statements with COUNT()
body: <p>I'll try to describe as best I can, but it's hard for me to wrap my whole head around this problem let alone describe it....</p>
  
  <p>I am trying to select multiple results in one query to display the current status of a database. I have the first column as one type of record, and the second column as a sub-category of the first column. The subcategory is then linked to more records underneath that, distinguished by status, forming several more columns.  I need to display every main-category/subcategory combination, and then the count of how many of each sub-status there are beneath that subcategory in the subsequent columns.  I've got it so that I can display the unique combinations, but I'm not sure how to nest the select statements so that I can select the count of a completely different table from the main query.  My problem lies in that to display the main category and sub category, I can pull from one table, but I need to count from a different table.  Any ideas on the matter would be greatly appreciated</p>
  
  <p>Here's what I have.  The count statements would be replaced with the count of each status:</p>
  
  <pre><code>  SELECT wave_num "WAVE NUMBER",
       int_tasktype "INT / TaskType",
       COUNT (1) total,
       COUNT (1) "LOCKED/DISABLED",
       COUNT (1) released,
       COUNT (1) "PARTIALLY ASSEMBLED",
       COUNT (1) assembled
  FROM (SELECT DISTINCT
               (t.invn_need_type || ' / ' || s.code_desc) int_tasktype,
               t.task_genrtn_ref_nbr wave_num
          FROM sys_code s, task_hdr t
         WHERE     t.task_genrtn_ref_nbr IN
                      (SELECT ship_wave_nbr
                         FROM ship_wave_parm
                        WHERE TRUNC (create_date_time) LIKE SYSDATE - 7)
               AND s.code_type = '590'
               AND s.rec_type = 'S'
               AND s.code_id = t.task_type),
       ship_wave_parm swp
  GROUP BY wave_num, int_tasktype
  ORDER BY wave_num
  </code></pre>
  
  <p>Image here: <a href="http://i.imgur.com/JX334.png" rel="nofollow">http://i.imgur.com/JX334.png</a></p>