CODEWITHSUNDEEP
sqlsql-serverconditional-statements
Katriel
Katriel

Reputation: 123612

SQL query counting the number of results where different conditions hold

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?

Upvotes: 5

Views: 1118

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

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;

Upvotes: 4

Related Questions