In MySQL I have the following table:
| date | work | code |
|---|---|---|
| 2022-01-01 11:41:24 | 10 | 1 |
| 2022-01-01 10:41:24 | 10 | 1 |
| 2022-01-03 09:41:24 | 0 | 0 |
| 2022-02-04 06:41:24 | 10 | 1 |
| 2022-02-05 05:41:24 | 40 | 1 |
My SQL code:
SELECT extract(MONTH FROM date) AS month, count(number) AS sum_number FROM be WHERE code='1' group by month
This is the result of this code:
Result
| month | sum_work |
|---|---|
| 1 | 20 |
| 2 | 50 |
How can I get this result and what do I need to change in my query?
work_days: each month how many day did they work
Final Result:
| month | work_days | sum_work |
|---|---|---|
| 1 | 1 | 20 |
| 2 | 2 | 50 |
>Solution :
You must count the distinct dates:
SELECT MONTH(date) AS month,
COUNT(DISTINCT DATE(date)) AS work_days,
COUNT(*) AS sum_number
FROM be
WHERE code='1'
GROUP BY month;