Need MYSQL database query solution-CodePudding

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

CodePudding user response：

You must count the distinct dates:

SELECT MONTH(date) AS month,   
       COUNT(DISTINCT DATE(date)) AS work_days, 
       COUNT(*) AS sum_number -- or SUM(work) as your expected result
FROM be 
WHERE code='1' 
GROUP BY month;

CodePudding user response：

If my guess is correct about your requirement, you want the number of days in each month having one or more records.

You can try something like this:

SELECT LAST_DAY(date) AS month_ending, 
       COUNT(number) AS sum_number
       COUNT (DISTINCT DATE(date)) AS workd_days
  FROM be 
  WHERE code='1'
 GROUP BY LAST_DAY(DATE)

LAST_DAY(date) is a better choice than EXTRACT(MONTH FROM date), because 2021-01-06 and 2022-01-06 both return 1 from the latter.