how to show return zero if no row return?



  • 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



  • 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')
            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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2