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

user_8127956 0
Viewed 109
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

informix mysql sql database
2 Answers

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
user_50708790

We can ensure that a query returns a row by having the query guarantee it.

Here's an example that retrieves exactly one row from an inline view i.

Then an outer join to another inline view s that gets a distinct list of values.

And then and then another outer join to table1.

SELECT t.a
     , COUNT(t.b) AS cnt
  FROM ( SELECT 1 AS n ) i 
  LEFT
  JOIN ( SELECT DISTINCT r.b 
           FROM table2 r
       ) s
  LEFT
  JOIN table1 t
    ON t.b      = s.b
   AND t.dated >= ...
 GROUP
    BY i.n
     , t.a

If inline view s returns no rows, the query should return 

 a      cnt
 ----   ---
 NULL     0
user_1077440
Related