How do I return 0 when my sql returns with no rows?

Question

select a, count (b) 
from table1 where b in ( select distict b from table2) 
and table1.dated>=DATE('yy/mm/dd') 
group by a;

In the above SQL, when I have count(b)>0 then it returns columns but when count=0 then no rows were returned

I did try UNION, NULLIF() and SELECT(SELECT()) as something but nothing worked.

I was expecting to get 0 returned if the count is equal to 0.

https://www.db-fiddle.com/#&togetherjs=2AkxeMUrPF

Answer 1

You could use:

select table1.a, count(DISTINCT table2.b) 
from table1
LEFT JOIN table2
  ON table1.b = table2.b
  AND table1.dated>=DATE('yy/mm/dd') -- this comparision is simply incorrect
group by table1.a