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