I have added a percentage calculation (%_SOLD - SOLD as a percentage of FOUND - line 3 below) to my query which is working to an extent but returning the IFNULL value instead of 0.00 when SOLD is being returned as NULL.
IFNULL(ROUND((SUM(CASE WHEN ((rows.type = 3) OR (rows.name = 'Labour')) THEN rows.price END)),2),0.00) AS FOUND,
IFNULL(ROUND((SUM(CASE WHEN (((rows.type = 3) OR (rows.name = 'Labour')) AND rows.status = 'A') THEN rows.price END)),2),0.00) AS SOLD,
IFNULL(ROUND((((SUM(CASE WHEN (((rows.type = 3) OR (rows.name = 'Labour')) AND rows.status = 'A') THEN rows.price END))*100.0)/(SUM(CASE WHEN ((rows.type = 3) OR (rows.name = 'Tyre Labour')) THEN rows.price END))),2),'na') AS '%_SOLD'
This is the output I am getting:
"FOUND" "SOLD" "%_SOLD"
"269.86" "0.00" "na"
"0.00" "0.00" "na"
Line 2 is correctly returning the IFNULL value because both FOUND and SOLD are 0.00, but I need Line 1 to return the calculated result (0.00) instead of the IFNULL value.
I tried inserting the IFNULL 0.00, used in the first 2 lines of the query, into the percentage calculation but it didn't work.
Please help, thanks!
