group by and union in oracle

Question

I would like to union 2 queries but facing an error in oracle.

select count(*) as faultCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='FAULT' 
union 
select count(*) as responseCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='RESPONSE' 
group by COMP_IDENTIFIER  
order by responseCount; 

Two queries run perfectly individually.but when using union,it says ORA-00904: "RESPONSECOUNT": invalid identifier

Answer 1

The error you've run into

In Oracle, it's best to always name each column in each UNION subquery the same way. In your case, the following should work:

select count(*) as theCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='FAULT' 
group by COMP_IDENTIFIER -- don't forget this
union 
select count(*) as theCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT='RESPONSE' 
group by COMP_IDENTIFIER  
order by theCount; 

See also:

Curious issue with Oracle UNION and ORDER BY

A good workaround is, of course, to use indexed column references as suggested by a_horse_with_no_name

The query you really wanted

From your comments, however, I suspect you wanted to write an entirely different query, namely:

select count(case AUDIT_CONTEXT when 'FAULT'    then 1 end) as faultCount,
       count(case AUDIT_CONTEXT when 'RESPONSE' then 1 end) as responseCount,
       COMP_IDENTIFIER 
from CORDYS_NCB_LOG 
where AUDIT_CONTEXT in ('FAULT', 'RESPONSE')
group by COMP_IDENTIFIER  
order by responseCount; 

Answer 2

The column names of a union are determined by the first query. So your first column is actually named FAULTCOUNT.

But the easiest way to sort the result of a union is to use the column index:

select ...
union 
select ...
order by 1;

You most probably also want to use UNION ALL which avoids removing duplicates between the two queries and is faster than a plain UNION

Answer 3

In Union or Union all query column names are determined by the first query column name.

In your query replace "order by responseCount" with "order by faultCount.