CODEWITHSUNDEEP
sqldatabaseoracle-databasecount
Ahmed Emad
Ahmed Emad

Reputation: 560

Can I return multiple counters in the same query result (oracle)?

I want to retrieve count of all rows match one value and at the same time return a different counter which will count subset of first counter results based on additional condition .

For example I want to retrieve below queries result in one query :

Select Count(*), 
from table
where cond1 = '1';

select Count(*)
from table
where cond1 = '1'
And Cond2 ='1';

can it be done in one query ?

Upvotes: 1

Views: 644

Answers (2)

jarlh
jarlh

Reputation: 44786

gmiley's answer is perfectly fine.

However, this one may be a bit faster - especially if cond1 is indexed!

select 
   count(*) as count1,
   count(case when cond2 = 1 then 1 end) as count2
from table
where cond1 = 1

Upvotes: 4

gmiley
gmiley

Reputation: 6604

You could do it using case and sum like this:

select 
   Sum(case when cond1 = 1 then 1 else 0 end) as count1,
   Sum(case when cond1 = 1 and cond2 = 1 then 1 else 0 end) as count2
from table;

Upvotes: 4

Related Questions