[SQL] Reuse query with different filters in same SELECT

Say you need to create one of those queries that produce a report or some statistics about the data stored in your DB tables. You will most likely need to reuse the same query but with different filters every time and gather the results in a single SELECT.

You will have a base query which gathers all the data you're interested into (the "total" one) and need to filter it multiple times (even on the same column) with different values.



Let's assume that said base query is something like:

SELECT t.column1, t.column3, t1.column2, t1.column5
FROM table t, table1 t1
WHERE [conditions]

Think of it as the starting set. It is to be used in a SELECT which groups on t.column1 and shows some statistics (COUNT, SUM, etc...) and has to be filtered even on t1.column2='value' and t1.column2='value1' to show in the same extraction finer details.

The deeper filtering is achieved through something like:

SELECT t.column1, t.column3, t1.column2, t1.column5
--we may even JOIN more tables than in the base query
FROM table t JOIN table1 t1 ON t.column1=t1.column1 JOIN table2 t2 ON t1.column5=t2.column
WHERE t1.column2='value'
    AND t2.column='other_value'

and:

--we do not have to filter in the exact same way as above
SELECT t.column1, t.column3, t1.column2, t1.column5
FROM table t JOIN table1 t1 ON t.column1=t1.column1
WHERE t1.column2='value1'

Think of these as subsets of the starting set. To extract the data them, we will just need to intersect them with the starting set.

Finally, piecing everything together would produce:

--the data we're interested to show
SELECT q.column1, --we group by this
    SUM(q.column3) as total, --total amount
    SUM(q1.column3) as total_value1, --total amount when filtered for value1
    COUNT(q2.column5) as total_value2 --total number when filtered for value2
FROM
--our base query (base set), with alias q
(
    SELECT t.column1, t.column3, t1.column2, t1.column5
    FROM table t, table1 t1
    WHERE [conditions]
) q,
--our first "filter" (subset1), with alias filter1
(
    SELECT t.column1, t.column3, t1.column2, t1.column5
    FROM table t JOIN table1 t1 ON t.column1=t1.column1 JOIN table2 t2 ON t1.column5=t2.column
    WHERE t1.column2='value'
        AND t2.column='other_value'
) filter1,
--our second "filter" (subset2), with alias filter2
(
    SELECT t.column1, t.column3, t1.column2, t1.column5
    FROM table t JOIN table1 t1 ON t.column1=t1.column1
    WHERE t1.column2='value1'
) filter2
--join the base query data with the filtered data. Here we basically intersect our base set with both subset1 and subset2, allowing us to produce the wanted output from the outer SELECT, thus successfully reusing the base query with different filters inside a single SELECT statement
WHERE filter1.column1(+)=q.column1
    AND filter2.column1(+)=q.column1
GROUP BY q.column1
ORDER BY q.column1 ASC;

Note that the (+)= syntax is available on Oracle, but you can join the three queries inside the WHERE condition on other DBs as well.