title: Oracle Select statement calculating count from two tables
body: <p>Consider the following <code>SELECT</code> statement where I get different counts from <code>licensee</code> &amp; <code>licensee_type</code> tables and am getting the results that I want.</p>
  
  <pre><code>SELECT
      licensee.license_type_id,
      COUNT(*) AS count_all,
      COUNT(CASE WHEN licensee.citizen = 'US' THEN 1 ELSE NULL END) AS count_a,
      COUNT(CASE WHEN licensee.citizen = 'Other' AND licensee.flag = 'Y' THEN 1 ELSE NULL END) AS count_b,
      COUNT(CASE WHEN licensee.flag = 'N' THEN 1 ELSE NULL END) AS count_c
  FROM licensee
  INNER JOIN license_type ON licensee.license_type_id = license_type.id
  GROUP BY licensee.license_type_id;
  </code></pre>
  
  <p>Scenario: The main table 'licensee' is split into two tables say 'licensee_us' and 'licensee_other' based on the column 'citizen'. Both the new tables doesn't have 'citizen' column. Table 'licensee_us' has records from 'licensee' table (citizen= 'US') &amp; similarly table 'licensee_other' has records from 'licensee' table (citizen = 'Other') &amp; both the tables has the JOIN-ing column 'license_type_id' &amp; column 'flag'.</p>
  
  <p>Now, What's the EFFICIENT way to get the same counts as the above SELECT query with the new two tables GROUP-ed by license_type_id? Let me know if any clarification needed. I am really looking for an 'EFFICIENT' way to do this. Something other than having to use UNION, if there is any.</p>