CODEWITHSUNDEEP
mysqlsql
Bisoux
Bisoux

Reputation: 522

How to show count zero in mysql query

I would like to get the number of employees that joined on a specific month. Below is my sample data.

enter image description here

If there are no employees that joined on a month the query should show zero like so,

enter image description here

I have come up with something like this but this only shows the months when there is a joinee on a specific month,

SELECT 
    COUNT(*) AS count,
    CASE
        WHEN MONTH(startDate) = 1 THEN 'JAN'
        WHEN MONTH(startDate) = 2 THEN 'FEB'
        WHEN MONTH(startDate) = 3 THEN 'MAR'
        WHEN MONTH(startDate) = 4 THEN 'APR'
        WHEN MONTH(startDate) = 5 THEN 'MAY'
        WHEN MONTH(startDate) = 6 THEN 'JUN'
        WHEN MONTH(startDate) = 7 THEN 'JUL'
        WHEN MONTH(startDate) = 8 THEN 'AUG'
        WHEN MONTH(startDate) = 9 THEN 'SEP'
        WHEN MONTH(startDate) = 10 THEN 'OCT'
        WHEN MONTH(startDate) = 11 THEN 'NOV'
        WHEN MONTH(startDate) = 12 THEN 'DEC'
    END AS SMonth
FROM
    Reports
GROUP BY SMonth

I am using mysql.

EDIT

The Reports table has data spanning different years for e.g data from 1988,2012,2015 etc so there could be data with joinee in Jan 2015 but not in Jan 2018

Upvotes: 0

Views: 1311

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133360

You can use a month sequence and mananeg for null value

SELECT 
    sum(case when startDate is null then 0 else 1 end)  AS count,
    CASE
        WHEN t.mon_num = 1 THEN 'JAN'
        WHEN t.mon_num = 2 THEN 'FEB'
        WHEN t.mon_num = 3 THEN 'MAR'
        WHEN t.mon_num = 4 THEN 'APR'
        WHEN t.mon_num = 5 THEN 'MAY'
        WHEN t.mon_num = 6 THEN 'JUN'
        WHEN t.mon_num = 7 THEN 'JUL'
        WHEN t.mon_num = 8 THEN 'AUG'
        WHEN t.mon_num = 9 THEN 'SEP'
        WHEN t.mon_num = 10 THEN 'OCT'
        WHEN t.mon_num = 11 THEN 'NOV'
        WHEN t.mon_num = 12 THEN 'DEC'
    END AS SMonth
FROM  (
        select  1 mon_num
        union
        select  2 
        union
        select  3 
        union
        select  4 
        union
        select  5 
        union
        select  6 
        union
        select  7 
        union
        select  8 
        union
        select  9 
        union
        select  10 
        union
        select  11 
        union
        select  12 
        
) t

LEFT JOIN Reports ON t.mon_num = MONTH(startDate) 
GROUP BY SMonth

Upvotes: 1

RusArtM
RusArtM

Reputation: 1300

Something like that may fit. But you can also get month from date functions.

SELECT 
    date_table.dt,
    MONTHNAME(date_table.dt) SMonth,
    COUNT(r.startDate) AS count
FROM 
    (
        SELECT 
            DATE(CONCAT(1970 + y1.num + y2.num*10, "-", mnth.num, "-01")) dt
        FROM
            (SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num UNION ALL SELECT 10 num) y1
        CROSS JOIN
            (SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num UNION ALL SELECT 10 num) y2
        CROSS JOIN
            (SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num UNION ALL SELECT 10 num UNION ALL SELECT 11 num UNION ALL SELECT 12 num) mnth
    ) date_table
LEFT JOIN 
    Reports r on FORMAT_DATE(r.startDate, "%Y-%m-01") = date_table.dt
WHERE
    date_table.dt BETWEEN "1991-07-24" AND NOW()
GROUP BY
    date_table.dt
ORDER BY
    date_table.dt
;

Upvotes: 2

JagaSrik
JagaSrik

Reputation: 718

This approach is working too, you should have mysql latest version.

with recursive cte (n) as (
select '2012-01-01' 
union all 
select n+interval 1 month
from cte 
where n < '2012-12-01'
) select count(emp.empno) as total,date_format(cte.n,'%M') as Month
 from cte left join emp
 on  date_format(cte.n,'%M')  = date_format(emp.hiredate,'%M') 
 group by cte.n ;

Upvotes: 1

zealous
zealous

Reputation: 7503

You can use coalesce(count(*), 0) or ifnull(count(*), 0) in your query.

SELECT 
    coalesce(COUNT(*), 0) AS count,
    CASE
        WHEN MONTH(startDate) = 1 THEN 'JAN'
        WHEN MONTH(startDate) = 2 THEN 'FEB'
        WHEN MONTH(startDate) = 3 THEN 'MAR'
        WHEN MONTH(startDate) = 4 THEN 'APR'
        WHEN MONTH(startDate) = 5 THEN 'MAY'
        WHEN MONTH(startDate) = 6 THEN 'JUN'
        WHEN MONTH(startDate) = 7 THEN 'JUL'
        WHEN MONTH(startDate) = 8 THEN 'AUG'
        WHEN MONTH(startDate) = 9 THEN 'SEP'
        WHEN MONTH(startDate) = 10 THEN 'OCT'
        WHEN MONTH(startDate) = 11 THEN 'NOV'
        WHEN MONTH(startDate) = 12 THEN 'DEC'
    END AS SMonth
FROM
    Reports
GROUP BY SMonth

Upvotes: 0

Related Questions