CODEWITHSUNDEEP
mysql
keepwalking
keepwalking

Reputation: 2654

Mysql: Sum by current date + 30 days

Table structure:

id int
revenue float
date date

I would like to SUM the revenue and group the result by date +30 days starting from a specific date until current date.

So i have data starting from 2015-01-05 so i would like to get the sum of the revenue starting from 2015-01-05 and each group should have +30 days like

2015/01/05 - 2015/02/04 
2015/02/04 - 2015/03/06
2015/03/06 - 2015/04/05
etc

So i want the result to be grouped in 30 days periods (not months)

Upvotes: 1

Views: 312

Answers (1)

Barmar
Barmar

Reputation: 781848

Use

GROUP BY FLOOR(DATEDIFF(date, '2015-01-05')/30)

DATEDIFF calculates the days between two dates. Then we divide by 30 to reduce them to 30-day blocks, and use FLOOR to remove the fraction.

Upvotes: 1

Related Questions