Yahia Baiba - 1 year ago 91
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|
+-----------+------------------------+
``````

``````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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download