CODEWITHSUNDEEP
mysqlcountgroup-bywhere-clause
Karan Kumar Mahto
Karan Kumar Mahto

Reputation: 103

Display 0 instead of blank if no record found in table

i want to display '0' when my query will return no records instead of blank

select count(*) AS count
from
    tbl a
where
    a.Id in ('45')
group by a.Id;

Upvotes: 1

Views: 95

Answers (3)

Keval
Keval

Reputation: 1859

This will work 

SELECT COUNT(*) as count FROM ( 
SELECT *
FROM
    tbl a
WHERE
    a.Id IN ('45')
GROUP BY a.Id) X;

Upvotes: 1

Murali
Murali

Reputation: 1114

I guess your query should be like this:

   select ifnull(count(*), 0) AS count
from
    tbl a
where
    a.Id in ('45')
group by a.Id;

See the sample attached how you can do this:

mysql> select ifnull(count(*), 0) as counttotal from Persons;
+------------+
| counttotal |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

Upvotes: 0

Julio Soares
Julio Soares

Reputation: 1190

select count(a.Id) AS count
from  tbl a
where a.Id in ('45');

The important bit is remove GROUP BY. But it would be better to restrict counting to one field

Upvotes: 1

Related Questions