How to select results with and without a condition?

Question

I have several queries where I need to do one query with a condition, and another one without the condition (typically to calculate a ratio of <results with condition>/<all results>):

-- all results
select count(*)
from somewhere

-- filtered results
select count(*)
from somewhere
where condition

The actual query is much longer with several joins and I would ideally love to have one that would encompass both cases. Is this possible?

Answer 1

You are describing conditional aggregation. Your pseudo-code would look like:

select count(*) as total,
    sum(case when <condition> then 1   else 0 end) as total_with_cond,
    avg(case when <condition> then 1.0 else 0 end) as ratio_with_cond
from somewhere

Where <condition> represents the predicate you want to check on each row.

In SQLite, that can evaluate predicate in numeric context, we can further shorten the syntax like so:

select count(*) as total,
    sum(<condition>) as total_with_cond,
    avg(<condition>) as ratio_with_cond
from somewhere