group by and union in oracle

sql oracle group-by

25,089

Solution 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;

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;

Solution 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

25,089

eshaa

Updated on June 09, 2020

Comments

eshaa almost 4 years
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
- user_0 almost 9 years
  
  You are creating a resulset with a column with the count. You are defining 2 names for the same column. There is a correct answer, I'm just wondering if you want a single column or 2 columns.
- eshaa almost 9 years
  
  i need 2 columns faultCount,responseCount
Lukas Eder almost 9 years

"The column names of a union are determined by the first query" - In fact, that's not entirely correct (for Oracle). It may well be that there is no available column name in ORDER BY. This might just be a bug, though
eshaa almost 9 years

I need the resultset columns to be faultCount,responseCount,COMP_IDENTIFIER
eshaa almost 9 years

It says ORA-00937: not a single-group group function
Bill almost 9 years

Then you don't want a UNION. UNION says build one data set with the data from the second query tacked onto the end of the data set from the first query - and line up the columns. JOINs are for putting together two queries onto the same row.
eshaa almost 9 years

Thank you.It works.But is there a way to enhance it.It takes more than 10 seconds to retrieve which is not acceptable in case.
a_horse_with_no_name almost 9 years

@user1650864 The union only has two columns. And one column can only have one name. You can't name the first column FAULTCOUNT and COMP_IDENTIFIER at the same time.
Lukas Eder almost 9 years

@user1650864: You should probably have an index: CREATE INDEX my_index ON CORDYS_NCB_LOG(COMP_IDENTIFIER, AUDIT_CONTEXT)
eshaa almost 9 years

hi,Can you post the query u r trying to explian
a_horse_with_no_name almost 9 years

@user1650864: I'm trying to explain your query.
eshaa almost 9 years

Still it takes 16 seconds !
Lukas Eder almost 9 years

Oh, perhaps, an additional AUDIT_CONTEXT in ('FAULT', 'RESPONSE') predicate might do the trick...