MySQL - if count in select subquery is null change to 0

Question

I have following query:

select `jobs`.*, 
(SELECT COUNT(user_jobs_application.id) as count FROM user_jobs_application
join job_shifts on job_shifts.id = user_jobs_application.job_shift_id
where job_shifts.jobs_id = jobs.id
and user_jobs_application.status = 1
group by user_jobs_application.users_id
) as entries
from `jobs` where `jobs`.`deleted_at` is null order by `id` desc limit 25 offset 0

The subquery in select will give null instead of 0. Can I change this so if the value is null it will show 0?

Answer 1

Removing the group by clause from the subquery should be sufficient. It is not needed anyway, since it groups on the column you are filtering on (and it it was needed, then I would mean the subquery may return more than one row, which would generate a runtime error).

select 
    j.*, 
    (
        select count(*) as count 
        from user_jobs_application uja
        join job_shifts js on js.id = uja.job_shift_id
        where js.jobs_id = j.id and uja.status = 1
    ) as entries
from jobs j
where j.deleted_at is null 
order by id desc limit 25 offset 0

Other changes to your query:

• presumably, user_jobs_application(id) is not nullable; if so, count(*) is good enough, and is more efficient than count(user_jobs_application.id)

• table aliases make the query easier to read and write