How to aggregate by month given start date and end date?-CodePudding

I have a table my_table that resembles:

id    start_date    end_date   
1     2019-12       2021-12
2     2017-11       2019-03
3     2016-09       2018-09

I have another tables date_values like with every date:

date_value
2016-09
2016-10

I want to get the output for every month:

month      num_people
2016-09    1
2016-10    1

How would I go about doing this?

I know I would need to GROUP BY month and then aggregate by COUNT(id), but I'm unsure of how to get the dates in the format I need. Would appreciate help, thanks!

CodePudding user response：

You can try to JOIN on date range, using BETWEEN, but your DateTime isn't a valid format, we need to use STR_TO_DATE function converts the string to DateTime then compare

Query 1:

    SELECT t2.date_value,COUNT(*) num_people
    FROM my_table t1
    INNER JOIN date_values t2
    ON STR_TO_DATE(CONCAT(t2.date_value,'-01'), '%Y-%m-%d')
    BETWEEN STR_TO_DATE(CONCAT(t1.start_date ,'-01'), '%Y-%m-%d')
    AND STR_TO_DATE(CONCAT(t1.end_date ,'-01'), '%Y-%m-%d')
    GROUP BY t2.date_value

Results:

| date_value | num_people |
|------------|------------|
|    2016-09 |          1 |
|    2016-10 |          1 |

I would suggest date_value,start_date, end_date might be DateTime type instead of string type.

if you want to show DATE_FORMAT data in yyyy-MM format, we can use DATE_FORMAT function.

SELECT DATE_FORMAT(now(),'%Y-%m')