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;