Yahia Baiba Yahia Baiba - 26 days ago 5
MySQL Question

Group by date every 10 days

I have this scheme:

+----+--+--------+--------------------+
| ID | Amount | paydate |
+----+-----------+--------------------+
| 1 | 200 |2016-11-05 |
+----+-----------+--------------------+
| 2 | 3000 |2016-11-10 |
+----+-----------+--------------------+
| 3 | 2500 |2016-11-11 |
+----+-----------+--------------------+
| ID | 100 |2016-11-21 |
+----+-----------+--------------------+
| 1 | 200 |2016-11-22 |
+----+-----------+--------------------+
| 2 | 3000 |2016-11-23 |
+----+-----------+--------------------+
| 3 | 2500 |2016-11-29 |
+----+-----------+--------------------+


How can I get the total Amount grouped by every 10 days like from the first of every month to the 10th then from 11th to 20th and from 21st to the end of the month?

to be shown like this :

+-----------+------------------------+
| Amount | paydate |
+-----------+------------------------+
| 3200 |2016-11-1 to 2016-11-10 |
+-----------+------------------------+
| 2500 |2016-11-11 to 2016-11-20|
+-----------+------------------------+
| 5800 |2016-11-21 to 2016-11-31|
+-----------+------------------------+

Answer
select      sum(Amount) as sum_amount
           ,case 
                when day(paydate) <= 10 then concat(DATE_FORMAT(paydate,'%Y-%m-01'),' to ',DATE_FORMAT(paydate,'%Y-%m-10'))
                when day(paydate) <= 20 then concat(DATE_FORMAT(paydate,'%Y-%m-11'),' to ',DATE_FORMAT(paydate,'%Y-%m-20'))
                else concat(DATE_FORMAT(paydate,'%Y-%m-21'),' to ',DATE_FORMAT(paydate,'%Y-%m-31'))
            end as paydate_period

from        t

group by    paydate_period
;

sum_amount  paydate_period
3200        2016-11-01 to 2016-11-10
2500        2016-11-11 to 2016-11-20
5800        2016-11-21 to 2016-11-31