Can IFNULL() return a string where the column value is int, using MySQL?

Question

My query

select cname, count(screening_occapancy.idclient) as 'Count'
from client, screening_occapancy 
where client.client_no = screening_occapancy.idclient 
group by cname

returns the following:

Name        Count
Name1        2
Name2        3
Name3        6

etc, now I want it the value in 'Count' to be "not found" if the value is null or 0, is that possible? I need something like that in my results:

    Name        Count
    Name1         2
    Name2         3
    Name3    "Not found"

The answer to the question in the title is yes: `ifnull(column, "Not Found")` — Barmar, Jun 11 '16 at 16:42

juergen d · Answer 1 · 2016-06-11T17:57:57.297

1

Use a left join to get 0 for all not found matches

select c.cname, 
       count(so.idclient) as 'Count'
from client c
left join screening_occapancy so on c.client_no = so.idclient 
group by c.cname

And BTW don't use the legacy implicit join syntax anymore. Use explicit joins.

edited Jun 11 '16 at 17:57

answered Jun 11 '16 at 16:23

juergen d

201,996
37
293
362

Akshey Bhat · Accepted Answer · 2016-06-11T16:31:41.033

1

    Select cname , 
case when Count is null or count =0 then 'Not found' 
else Count end as count
 from
    (select cname,
 count(screening_occapancy.idclient) as 'Count' 
    from client left join screening_occapancy 
    on
    client.client_no = screening_occapancy.idclient group by cname) t

Write a wrapper query above your query to check count column

edited Jun 11 '16 at 16:31

answered Jun 11 '16 at 16:23

Akshey Bhat

8,227
1
20
20

Working. Thanks a lot. – Gilzy Jun 11 '16 at 16:33

score 0 · Answer 3 · answered Jun 11 '16 at 17:34

select cname, IF(count(screening_occapancy.idclient)!=0,count(screening_occapancy.idclient),'NOT FOUND') as 'Count'
from client, screening_occapancy 
where client.client_no = screening_occapancy.idclient 
group by cname

or if count returns null?

select cname, IFNULL(count(screening_occapancy.idclient),'NOT FOUND') as 'Count'
from client, screening_occapancy 
where client.client_no = screening_occapancy.idclient 
group by cname

Can IFNULL() return a string where the column value is int, using MySQL?

3 Answers3