CODEWITHSUNDEEP
sqloracle-databasecount
Marco
Marco

Reputation: 1232

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

Upvotes: 2

Views: 6917

Answers (4)

Jenna Leaf
Jenna Leaf

Reputation: 2452

This should work too

(select equipment_name, count(*) as cnt from alarms.new_alarms 
  where equipment_name in (
     select eqp from ne_db.ne_list
  ) group by equipment_name
)union( 
   select equipment_name, 0 as cnt from alarms.new_alarms 
   where equipment_name not in (
     select eqp from ne_db.ne_list
   ) group by equipment_name
) order by equipment_name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If the table ne_list has no duplicates, then you can do a left join. That assumption may not be true, so the safest way to convert this is by removing duplicates in a subquery:

select count(1), ne.equipment_name 
from   alarms.new_alarms ne left outer join
       (select distinct eqp
        from ne_db.ne_list
       ) eqp
       on ne.equipment_name = eqp.eqp
Group by ne.equipment_name

Upvotes: 2

John Woo
John Woo

Reputation: 263723

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

Upvotes: 4

Andomar
Andomar

Reputation: 238086

You could use a left join:

select  ne.equipment_name 
,       count(na.equipment_name)
from   ne_db.ne_list ne
left join   
       alarms.new_alarms na
on     ne.eqp = na.equipment_name
group by 
       ne.equipment_name

Upvotes: 1

Related Questions