CODEWITHSUNDEEP
sqloracle-databasejoincount
ktsigkounis
ktsigkounis

Reputation: 103

SQL Query Count results as zero event record is not exist

I have an issue to an SQL Query (Oracle database).

I have two tables. One table is "ACCIDENTS " and the other is "REASONS". This second is table has some predefined default values.

 - REASONS -

Reason 1
Reason 2
Reason 3
Reason 4

Now in the ACCIDENTS table we insert some accidents and reasons from the previous table like below

 - ACCIDENTS -

Accident 1  - Reason 1
Accident 2  - Reason 1
Accident 3  - Reason 4

All I want to get the count of accidents GROUP BY all 4 reasons even if a reason does not exist in ACCIDENTS table. In this case I want to get Count = 0 like below:

REASONS   COUNT (of Accidents)
Reason 1    2
Reason 2    0
Reason 3    0
Reason 4    1

Unsuccessfully I have already tried different type of JOIN tables but I don't get as results Reason 2 and Reason 3 because they don't exist in ACCIDENTS table. Every time the result is:

REASONS   COUNT (of Accidents)
Reason 1    2
Reason 4    1

Any solutions/thoughts ?

Thanks in advance!

UPDATE !

This is the query:

SELECT R.REASON_NAME AS REASON, COUNT(A.ID) AS COUNT_OF_ACCIDENTS
FROM ACCIDENTS A
RIGHT JOIN REASONS R ON R.ID = A.REASON_ID
WHERE EXTRACT(YEAR FPOM A.DATE_OF_ACCIDENT) = 2017
GROUP BY R.REASON_NAME

If i remove the WHERE statement then I get all REASONS correctly but the Where for Year in Accidents table is mandatory.

Upvotes: 1

Views: 204

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want a left join and aggregation:

select r.reason, count(a.reason)
from reasons r left join
     accidents a
     on r.reason = a.reason and
        a.date_of_accident >= date '2017-01-01' and
        a.date_of_accident < date '2018-01-01'
group by r.reason;

Upvotes: 1

Related Questions