CODEWITHSUNDEEP
sqloracle-database
Volatil3
Volatil3

Reputation: 14978

SQL: return 0 count in case no record is found

A very simple issue as it appears but somehow not working for me on Oracle 10gXE.

Based on my SQLFiddle, I have to show all staff names and count if present or 0 if no record found having status = 2

How can I achieve it in a single query without calling Loop in my application side.

Upvotes: 2

Views: 9764

Answers (3)

ChrisCamp
ChrisCamp

Reputation: 682

Move filter into the LEFT JOIN , also use COALESCE to have your results display 0 instead of null as you requested in your question

select S.NAME,COALESCE(ISTATUS.STATUS,0),COUNT(ISTATUS.Q_ID) as TOTAL
from   STAFF S
LEFT  OUTER JOIN  QUESTION_STATUS ISTATUS
ON S.ID = ISTATUS.DONE_BY
AND  ISTATUS.STATUS =2
GROUP BY S.NAME,ISTATUS.STATUS

Upvotes: 1

Sebas
Sebas

Reputation: 21532

I corrected your sqlfiddle: http://sqlfiddle.com/#!4/90ba0/12

The rule of thumb is that the filters must appear in the ON condition of the table they depend on.

Upvotes: 2

user743382
user743382

Reputation: 

SELECT S.NAME,ISTATUS.STATUS,COUNT(ISTATUS.Q_ID) as TOTAL
FROM STAFF S
LEFT OUTER JOIN  QUESTION_STATUS ISTATUS
ON S.ID = ISTATUS.DONE_BY
AND ISTATUS.STATUS = 2 <--- instead of WHERE
GROUP BY S.NAME,ISTATUS.STATUS

By filtering in the WHERE clause, you filter too late, and you remove STAFF rows that you do want to see. Moving the filter into the join condition means only QUESTION_STATUS rows get filtered out.

Note that STATUS is not really a useful column here, since you won't ever get any result other than 2 or NULL, so you could omit it:

SELECT S.NAME,COUNT(ISTATUS.Q_ID) as TOTAL
FROM STAFF S
LEFT OUTER JOIN  QUESTION_STATUS ISTATUS
ON S.ID = ISTATUS.DONE_BY
AND ISTATUS.STATUS = 2
GROUP BY S.NAME

Upvotes: 5

Related Questions