Geek asks and answers

SQL query that counts the number of results in which various conditions are met

I have a request 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 good, and gives me a set of results with names and values. But I really care about the number of problems without the WHERE clause, and then with condition 1, then conditions 1 + 2, then conditions 1 + 2 + 3. I would like to write

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

but unfortunately I can’t. Is there a good way to do this?

+5
source share
1 answer

You can use an expression CASEfor this:

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 an RDBMS that supports a table statement PIVOTsuch as MS SQL Server or Oracle, you can use it directly for this.

SQL Server, PIVOT, :

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

More articles:


All Articles