Want to return 0 in case of no rows

Question

select 
    IMEI_ID, dev_status, nvl(count(1), 0) AS Count
from 
    device
where 
    IMEI_ID IN (284700, 250779, 320700, 339622, 323400)
    and Dev_status = 'ACTIVE'
group by 
    IMEI_ID,dev_status;

For a few IMEI_ID, no rows are returned. I want the count to be Zero (0) for these missing rows.

Thanks

Not sure what `nvl` is in your script, you can try `coalesce(count(1),0)` — Cheng, Jul 12 '19 at 10:10
please mention the sql dialect or the database product you are using — nomadSK25, Jul 12 '19 at 10:13
You need a on-the-fly constructed table of `IMEI_ID`s this is sql product specific (`nvl` - Oracle i guess?). Then left join your query. — Serg, Jul 12 '19 at 10:13
See if this helps https://stackoverflow.com/questions/39532916/return-0-in-group-by-when-count-is-null — Sukhi, Jul 12 '19 at 10:33

score 1 · Answer 1 · answered Jul 12 '19 at 10:23

1

For Oracle

with i(ime_id) as
( 
    SELECT 284700 FROM DUAL UNION ALL
    SELECT 250779 FROM DUAL UNION ALL
    SELECT 320700 FROM DUAL UNION ALL
    SELECT 339622 FROM DUAL UNION ALL
    SELECT 323400 FROM DUAL 
)
select i.IMEI_ID, d.dev_status, nvl(count(d.ime_id),0) AS Count
from i
left join device d on i.ime_id = d.IMEI_ID
   and d.Dev_status='ACTIVE'
group by i.IMEI_ID, d.dev_status;

answered Jul 12 '19 at 10:23

Serg

22,285
5
21
48

And the modern MySQL and MariaDB versions which also support CTE and tableless selects with DUAL dummy table – Raymond Nijland Jul 12 '19 at 11:03
1

@RaymondNijland, 'nvl` in OP points to Oracle, so I didn't mention other dbmses. – Serg Jul 12 '19 at 11:09
true `nvl()` is Oracle only but it can be replaced with `coalesce()` to support other dbmses as well.. – Raymond Nijland Jul 12 '19 at 13:32

Robert Kock · Answer 2 · 2019-07-12T10:26:20.597

Create a (temporary) table with the requested IMEI_ID and then LEFT OUTER JOIN on the device:

SELECT          imei_ids.imei_id,
                device.dev_status,
                COUNT(device.dev_status)
FROM            imei_ids
LEFT OUTER JOIN device
             ON imei_ids.imei_id = device.imei_id
            AND device.dev_status = 'ACTIVE'
GROUP BY        imei_ids.imei_id,
                device.dev_status;

Want to return 0 in case of no rows

2 Answers2