Group by months using PERIOD_DIFF mysql

Question

I have the following table name surveydb,I use mysql 5.7

PERIODE	SURVEY
2020-02-03	2
2020-02-04	3
2020-03-10	5
2020-03-11	4
2020-07-28	1
2020-08-28	3
2020-11-28	4

I Used the following query in mysql,

SELECT `SURVEY`,MIN(`PERIODE`) AS `Range Start`, MAX(`PERIODE`) AS `Range End`, COUNT(*) AS `Count`
FROM `surveydb`
GROUP BY FLOOR(PERIOD_DIFF(@T1, DATE_FORMAT(`PERIODE`, '%Y%m')) / 3)

I want to group by 3 months interval but the result is different,

only 1 survey is shown for date interval from 2020-02-03 to 2020-03-11 although they have different survey values, how do I show all surveys for the range of each group

SURVEY	RANGE START	RANGE END	count
2	2020-02-03	2020-03-11	4
1	2020-07-28	2020-08-28	2

I can not show all surveys within the months interval, for example from 2020-02-03 to 2020-03-11

Instead, I want to get results like this:

SURVEY	RANGE START	RANGE END	count
2	2020-02-03	2020-03-11	4
3	2020-02-03	2020-02-03	4
5	2020-02-03	2020-02-03	4
4	2020-02-03	2020-02-03	4
1	2020-07-28	2020-08-28	2

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query - and clarify which version of MySQL you're using — Strawberry, Dec 26 '20 at 07:56

score 0 · Answer 1 · answered Dec 26 '20 at 08:26

0

seems you need group by SURVEY too

SELECT `SURVEY`,MIN(`PERIODE`) AS `Range Start`, MAX(`PERIODE`) AS `Range End`, COUNT(*) AS `Count`
FROM `surveydb`
GROUP BY `SURVEY`, FLOOR(PERIOD_DIFF(@T1, DATE_FORMAT(`PERIODE`, '%Y%m')) / 3)

answered Dec 26 '20 at 08:26

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered Dec 26 '20 at 11:40

Do you want a window functions rather than aggregation?

select survey,
    min(periode) over(partition by floor(period_diff(@t1, date_format(periode, '%y%m')) / 3)) as range_start, 
    min(periode) over(partition by floor(period_diff(@t1, date_format(periode, '%y%m')) / 3)) as range_end, 
    count(*)     over(partition by floor(period_diff(@t1, date_format(periode, '%y%m')) / 3)) as cnt
from surveydb

We could shorten the query with a named window:

select survey,
    min(periode) over w as range_start, 
    min(periode) over w as range_end, 
    count(*)     over w as cnt
from surveydb
window w as (partition by floor(period_diff(@t1, date_format(periode, '%y%m')) / 3))

Group by months using PERIOD_DIFF mysql

2 Answers2