sql - MySQL COUNT() to return 0 -
i have query looks this:
select app.application_id, j.job_number, j.job_id, j.job_title, j.job_city, j.job_state, p.person_id candidate_id, p.first_name, p.last_name, app.start_date, ope1.percent_complete, max(case when r.role_display_name = 'eng - recruiter' (select case when count(last_name) = 0 'unassigned' else count(last_name) end uname users join job_roles on job_roles.user_id = users.user_id job_id = j.job_id , role_id = r.role_id ) else '' end) role_3
my problem count(last_name)
not return 0, because there no records returned, there no value of null
. makes sense, have tried wrapping in ifnull()
, isnull()
, none of them seem fix problem. how can return 0 when there no records? need another subquery inside count()
aggregate? not use subquery....
if understand correctly want can try rewrite way
select ... ,max(case when r.role_display_name = 'eng - recruiter' coalesce(nullif( ( select count(last_name) users join job_roles on job_roles.user_id = users.user_id job_id = j.job_id , role_id = r.role_id ), 0), 'unassigned') else '' end) role_3 ...
Comments
Post a Comment