SQL query counting the number of results where different conditions hold

Question

I have a query that looks like

SELECT ju.name,
    COUNT(DISTINCT p.value) AS nproblems
FROM #problems p
JOIN <thing> ju ON <whatever>
WHERE <condition 1>
    AND <condition 2>
    AND <condition 3>
GROUP BY ju.name
ORDER BY nproblems DESC

This is fine, and gives me a result set with names and values. But what I really care about is the number of problems without the WHERE clause, then with just condition 1, then conditions 1+2, then conditions 1+2+3. I'd like to write

SELECT ju.name,
    COUNT(DISTINCT p.value WHERE <condition 1>) foo,
    COUNT(DISTINCT p.value WHERE <condition 2>) bar,
...

but sadly I can't. Is there a nice way of doing this?

Answer 1

You can use the CASE expression to do so:

SELECT ju.name,
    SUM(CASE WHEN <condition 1> THEN 1 ELSE 0 END) AS foo,
    SUM(CASE WHEN <condition 1> THEN 1 ELSE 0 END) AS bar,
    ...
FROM #problems p
JOIN <thing> ju ON <whatever>
GROUP BY ju.name
ORDER BY nproblems DESC;

However: If you are using a RDBMS that supports the PIVOT table operator like MS SQL Server or Oracle, you can use it to do so, directly.

---

Since you are using SQL Server, you can use the PIVOT table operator to do so:

SELECT *
FROM 
(
) AS t
PIVOT
(
   COUNT(value)
   FOR name IN(...)
) AS p;