CODEWITHSUNDEEP
mysql
kretinus
kretinus

Reputation: 31

How to handle where Date if no records for date

I'm setting up a graph that display number of new contracts per employee per month. Not every month as records in my table, how do display 0 as contracts count for every employee on month that have no records?

SELECT EmployeeName, SUM(contract) as nbcontract
FROM table
WHERE month(Date) = month(now())
group by EmployeeName

Current result when no records for month(now):

EmployeeName|nbcontract

Expected result:

EmployeeName|nbcontract
employee1  0
employee2  0
employee3  0
...

Where EmployeeName displays all possible distinct value of EmployeeName

Any hint would be appreciated!

Upvotes: 0

Views: 18

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Use conditional aggregation:

SELECT
    EmployeeName,
    SUM(CASE WHEN MONTH(date) = MONTH(NOW()) THEN contract ELSE 0 END) AS nbcontract
FROM yourTable
GROUP BY
    EmployeeName;

This approach would guarantee that every employee would appear in the result set, even if he has no contracts in the current month.

Upvotes: 1

Related Questions