how to show return zero if no row return?

All we need is an easy explanation of the problem, so here it is.

I spent my half day on it, but still did not find solution, I tried COALESCE,IFNULL, IS NULL, IF conditions, <=>, IS NOT NULL etc but nothing work for me. The query is

SELECT 

COALESCE(SUM(leave_duration),0) AS On_leaves 

FROM `emp_leave` 
WHERE DATE_FORMAT(start_date,'%Y-%m-%d')>=DATE_FORMAT(CURDATE(), '%Y-%m-%01') 
AND DATE_FORMAT(end_date,'%Y-%m-%d')<=DATE_FORMAT(CURDATE(), '%Y-%m-%31') AND em_id =724 AND leave_status= 'Approved' GROUP BY em_id

I want it show 0 if there is no leaves in table :/
please help

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The reason COALESCE does not work in your example is that there are no rows to apply that function to. As @Akina suggest in his comment, you can solve it by using your query as a sub-query. Another alternative is to add a row with 0 as duration (assuming positive durations) and then pick the largest duration:

SELECT MAX(On_leaves)
FROM (
    SELECT SUM(leave_duration) AS On_leaves 
    FROM emp_leave 
    WHERE DATE_FORMAT(start_date,'%Y-%m-%d')
        >=DATE_FORMAT(CURDATE(), '%Y-%m-%01') 
      AND DATE_FORMAT(end_date,'%Y-%m-%d')
        <=DATE_FORMAT(CURDATE(), '%Y-%m-%31') 
      AND em_id =724 
      AND leave_status= 'Approved'
   GROUP BY em_id -- is this correct?      

   UNION ALL

   SELECT 0 AS On_leaves
) AS T   

I’m a bit suspicious of your GROUP BY clause. If you group by em_id you may end up with several sums, is that your intention?

Using DATE_FORMAT in your predicates will make it difficult to utilize indexes and will be expensive. If start_date, end_date are dates, consider removing DATE_FORMAT

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply