title: Oracle: Combine two group by queries (which use aggregate function count()) by union or so to get a consolidated result
body: <p>I have two tables. <strong>TABLE_A</strong> and <strong>TABLE_B</strong>.</p>
  
  <p>Both tables maintain columns to save <strong>CREATION_USER</strong>. But this column has different name in respective tables.</p>
  
  <p>My motive is to get a count of records each user has created in both tables.
  That is, combining result of these two queries with few conditions. The user name should not get repeated and for user names who have created records in both tables, the count should be their sum.</p>
  
  <pre><code>SELECT A.CREATION_USER_A AS "USER",
  COUNT(*)
  FROM TABLE_A A
  GROUP BY A.CREATION_USER_A;
  
  SELECT B.CREATION_USER_B AS "USER",
  COUNT(*)
  FROM TABLE_B B
  GROUP BY B.CREATION_USER_B;
  </code></pre>
  
  <p>For e.g.,</p>
  
  <p>USER_A has created 2 records in TABLE_A,</p>
  
  <p>USER_B has created 3 records in TABLE_B and</p>
  
  <p>USER_C has created 4 records in TABLE_A and 3 records in TABLE_B.</p>
  
  <p>So the output should look like this:</p>
  
  <pre><code>|  USER  | COUNT |
  | USER_A | 2     |
  | USER_B | 3     |
  | USER_C | 7     |
  </code></pre>
  
  <p>I have written a query which does this but it performs really bad.</p>
  
  <pre><code>SELECT A.CREATION_USER_A AS "USER",
  (COUNT(A.CREATION_USER_A)+(SELECT COUNT(CREATION_USER_B) FROM TABLE_B WHERE CREATION_USER_B = A.CREATION_USER_A)) AS "COUNT"
  FROM TABLE_A A
  GROUP BY A.CREATION_USER_A
  UNION
  SELECT B.CREATION_USER_B,
  COUNT(B.CREATION_USER_B)
  FROM TABLE_B B
  WHERE B.CREATION_USER_B NOT IN (SELECT CREATION_USER_A FROM TABLE_A)
  GROUP BY B.CREATION_USER_B;
  </code></pre>
  
  <p>Please suggest a way to get this done.</p>