Mysql – Group By days interval (aging type)

MySQL

I would lIke to have Mysql group by days interval for example group by every 20 days from current day, like 1 – 20, 21 – 40, 41 – 60 and son on up to lets 120 days. The user can choose the days interval and up to how many days

Best Answer

select floor(days / 20) as daygroup, count(*)
from mytable
group by daygroup

Related Solutions

Group by Continuous Days in MySQL – How to Guide

SELECT from_id,to_id,
  CONCAT_WS(' - ',
    MIN(dt),
    CASE WHEN MAX(dt)>MIN(dt) THEN MAX(dt) END
  ) As intervals
FROM (
  SELECT from_id,to_id,
    CASE WHEN dt=@last_ci+INTERVAL 1 DAY THEN @n ELSE @n:=@n+1 END AS g,
    @last_ci := dt As dt
  FROM
    t, (SELECT @n:=0) r
  ORDER BY
    dt
) s
GROUP BY
  g

FIDDLE

MySQL: summary query for date activity by last 7 days, week and all year totals

SELECT * FROM
(
    Status,Date, Count(*) RowCount FROM Customer
    WHERE Date > (DATE(NOW()) - INTERVAL 7 DAY)
    GROUP BY Status,Date
    UNION
    Status,WEEK(Date) WK,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,WK
    UNION
    Status,'YearToDate' YTD,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,YTD
) A ORDER BY Status,Date;

Best Answer

Related Solutions

Group by Continuous Days in MySQL – How to Guide

MySQL: summary query for date activity by last 7 days, week and all year totals

Related Question