title: How to show the total as last row in a result set which contains the union of multiple selects from multiple tables?
body: <p>I have the below tables.</p>
  
  <p>CREATE TABLE employee (
      interface_name   NVARCHAR2(80),
      rule_name        NVARCHAR2(255)
  );</p>
  
  <p>INSERT ALL  INTO employee ( interface_name, rule_name) VALUES ('EMP01', 'Invalid dept')</p>
  
  <p>INTO employee ( interface_name, rule_name) VALUES ('EMP01', 'Invalid dept')</p>
  
  <p>SELECT * FROM dual;</p>
  
  <p>CREATE TABLE department (
      interface_name   NVARCHAR2(80),
      rule_name        NVARCHAR2(255)
  );</p>
  
  <p>INSERT ALL  INTO department ( interface_name, rule_name) VALUES ('DEPT01', 'Missing Dept id')</p>
  
  <p>INTO department ( interface_name, rule_name) VALUES ('DEPT01', 'Missing Dept id')</p>
  
  <p>INTO department ( interface_name, rule_name) VALUES ('DEPT01', 'Missing Dept id')</p>
  
  <p>SELECT * FROM dual;</p>
  
  <p>and I would like to get an output like this from the above tables. </p>
  
  <pre><code>|---------------------|------------------|----------------|
  |   INTERFACE_NAME    |     RULE_NAME    |   ERROR_COUNT  |
  |---------------------|------------------|----------------|
  |       EMP01         |   Invalid dept   |      2         |
  |---------------------|------------------|----------------|
  |       DEPT01        | Missing Dept id  |      3         |
  |---------------------|------------------|----------------|
  |       Total         |         -        |      5         |
  |---------------------|------------------|----------------|
  </code></pre>
  
  <p>But while executing the below query </p>
  
  <pre><code>select coalesce(interface_name,to_nchar( 'Total')) as interface_name, coalesce( rule_name,to_nchar('-')) as rule_name, to_number(sum(error_count)) as error_count
  from (
      SELECT interface_name, rule_name, COUNT(*) error_count FROM emp_error GROUP BY interface_name, rule_name
      UNION 
      SELECT interface_name, rule_name, COUNT(*) error_count FROM dept_error GROUP BY interface_name, rule_name
      ) t
  group by rollup(interface_name,rule_name,error_count);
  </code></pre>
  
  <p>I'm getting the below.</p>
  
  <pre><code>|---------------------|------------------|----------------|
  |   INTERFACE_NAME    |     RULE_NAME    |   ERROR_COUNT  |
  |---------------------|------------------|----------------|
  |       EMP01         |   Invalid dept   |      2         |
  |---------------------|------------------|----------------|
  |       EMP01         |   Invalid dept   |      2         |
  |---------------------|------------------|----------------|
  |       EMP01         |         -        |      2         |
  |---------------------|------------------|----------------|
  |       DEPT01        | Missing Dept id  |      3         |
  |---------------------|------------------|----------------|
  |       DEPT01        | Missing Dept id  |      3         |
  |---------------------|------------------|----------------|
  |       DEPT01        |         -        |      3         |
  |---------------------|------------------|----------------|
  |       Total         |         -        |      5         |
  |---------------------|------------------|----------------|
  </code></pre>
  
  <p>What is the error in this query?</p>