oracle query not getting count as 0 with group by and count query , instead getting empty row

Question

This is the query and we dont have data for location# =83 but i want output as cnt=0

SELECT
            COUNT(1) AS cnt , location# as  record_no
        FROM
            test
        WHERE
                cny# = 1
            AND location# = 83 group by location#
    

getting the output as empty row, instead expected out similar to this like

     cny
---|-----
 1    0

With below query i m getting the expected output

SELECT
            COUNT(1) AS cnt 
        FROM
            test
        WHERE
                cny# = 1
            AND location# =83 

How can i get same output as shown in expected above with using group by and adding the column in select as in top query i have shown

Answer 1

Sample data:

SQL> select * from test;

 LOCATION#       CNY#
---------- ----------
         1          1
         1          1
        83         22

This, as you know, works:

SQL> select count(*)
  2  from test
  3  where cny# = 1
  4    and location# = 83;

  COUNT(*)
----------
         0

You'd want to see that zero along with location# = 83, but - there's none:

SQL> select location#, count(*)
  2  from test
  3  where cny# = 1
  4    and location# = 83
  5  group by location#;

no rows selected

---

One option is to use self-join; just to check what's going on without filter on location#:

SQL> select a.location#, count(distinct b.rowid) cnt
  2  from test a left join test b on a.location# = b.location# and a.cny# = 1
  3  --where a.location# = 83
  4  group by a.location#;

 LOCATION#        CNT
---------- ----------
        83          0
         1          2

With filter:

SQL> select a.location#, count(distinct b.rowid) cnt
  2  from test a left join test b on a.location# = b.location# and a.cny# = 1
  3  where a.location# = 83
  4  group by a.location#;

 LOCATION#        CNT
---------- ----------
        83          0