Marco Marco - 5 months ago 5
SQL Question

Oracle query - how to make count to return values with 0

How can I make a count to return also the values with 0 in it.

Example:

select count(1), equipment_name
from alarms.new_alarms
where equipment_name in (
select eqp from ne_db.ne_list)
Group by equipment_name


It is returning only the counts with values higher than 0 , but I need to know the records that are not returning anything.

Any help is greatly appreciated.

thanks,

Marco

Answer

Try using LEFT JOIN,

SELECT  a.eqp, COUNT(b.equipment_name) totalCount
FROM    ne_db.ne_list a
        LEFT JOIN alarms.new_alarms b
            ON a.eqp = b.equipment_name
GROUP   BY a.eqp
Comments