Zero if no value returned

Question

I have the following query:

SELECT  
    f.name, 
    COUNT(distinct(p.id))
FROM 
    problems p INNER JOIN problem_list pl on p.problem_list_id = pl.id 
    FULL OUTER JOIN facilities f on f.id = p.facility
WHERE  
    p.problem_list_id = '100' 
    AND f.name in ('CRJ','TVRJ','WRJ')
GROUP BY
    f.name
ORDER BY
    f.name

When this query is run, sometimes one of the facilities does not return a result. In that case, I'd still like my result set to show that facility, but return a 0.

For example:

CRJ       |          0|
TVRJ      |         12|
WRJ       |          2|

I've tried to use coalesce, and adjusting my joins on the facility table, but it doesn't seem to be working.

Possible duplicate of [How to include "zero" / "0" results in COUNT aggregate?](http://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate) — rory.ap, Nov 04 '16 at 14:42

score 3 · Accepted Answer · answered Nov 04 '16 at 14:43

3

I don't think that a FULL JOIN is needed, just a LEFT JOIN:

SELECT  
    f.name, 
    ISNULL(COUNT(DISTINCT p.id),0) N
FROM 
    facilities f 
    LEFT JOIN ( SELECT * 
                FROM problems
                WHERE problem_list_id = '100') p 
        ON f.id = p.facility
    LEFT JOIN problem_list pl 
        ON p.problem_list_id = pl.id 
WHERE  
    f.name in ('CRJ','TVRJ','WRJ')
GROUP BY
    f.name
ORDER BY
    f.name;

answered Nov 04 '16 at 14:43

Lamak

69,480
12
108
116

Why the need for the embedded query? – Derrick Moeller Nov 04 '16 at 14:53
2

@FrumRoll It's exactly the same as using that filter as a join condition. I prefer this way of writing it because it's more clear (to me) that I'm filtering some rows – Lamak Nov 04 '16 at 14:54

Derrick Moeller · Answer 2 · 2016-11-04T15:26:44.617

1

Your original query started with problems, if you want to include facilities that haven't had problems you likely want to start with facilities.

SELECT f.name, ISNULL(COUNT(DISTINCT p.id), 0)
FROM facilities f
LEFT JOIN problems p ON p.facility = f.id AND p.problem_list_id = '100'
LEFT JOIN problem_list pl ON pl.id = p.problem_list_id
WHERE f.name in ('CRJ', 'TVRJ', 'WRJ')
GROUP BY f.name
ORDER BY f.name

edited Nov 04 '16 at 15:26

answered Nov 04 '16 at 14:44

Derrick Moeller

4,808
2
22
48

Zero if no value returned

2 Answers2