SQL Query have 0 instead of 'null'

Question

How do I make 'null' data represented as 0 instead of 'null'? See query below and screenshot :)

SELECT Supervisor,
SUM(CASE WHEN DAYOFWEEK(workdate) = 2 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Monday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 3 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Tuesday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 4 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Wednesday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 5 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Thursday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 6 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Friday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 7 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Saturday`
JOIN payroll.employeehours ON employeedatanew_copy.`ID Number` = employeehours.employeeid
WHERE employeehours.workdate BETWEEN '$staticstart' AND '$staticfinish'
GROUP BY supervisor

enter image description here

like so? does not work.... SUM(CASE WHEN DAYOFWEEK(workdate) = 2 THEN (CASE WHEN employeehours.paycode = '02 Overtime 1.5' then (IFNULL(IF(Other Rate = 0, (employeehours.employeehours * Base Rate),(employeehours.employeehours * Other Rate)),0)) end) END) Monday, — atomapps
– atomapps, Commented Dec 5, 2013 at 1:31
got it! IFNULL(SUM(CASE WHEN DAYOFWEEK(workdate) = 2 THEN (CASE WHEN employeehours.paycode = '02 Overtime 1.5' then (IF(Other Rate = 0, (employeehours.employeehours * Base Rate),(employeehours.employeehours * Other Rate))) end) END),0) Monday, — atomapps
– atomapps, Commented Dec 5, 2013 at 1:31
Sure. COALESCE (which is a standard function) would've worked as well the same way. — PM 77-1
– PM 77-1, Commented Dec 5, 2013 at 1:34

John Conde · Accepted Answer · 2013-12-05 01:23:40Z

2

Use COALESCE()

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

COALESCE(employeehours.employeehours * `base rate`, 0)

answered Dec 5, 2013 at 1:23

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

atomapps Over a year ago

this is useful to me for future but not in this case, as I am looking to get rid of the nulls, thanks anyway!

Collectives™ on Stack Overflow

SQL Query have 0 instead of 'null'

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related