Group rows by Year Band Interval

Question

I have a table (tbl_people), in this table I have a datetime field I want to group and count the records in groups of 10 years... The result should be something like:
| count    | year           |
| 1000     | 1980-1989 |
| 250       | 1990-1999 |

I can write multiple queries to do it, but that means I'll have to write different queries for each range. Is there a way to dynamically increment from the least year upwards (10 year intervals) and count the number of records within the intervals?

Answer 1

First calculate the decade for each row

select floor(year(`year`) / 10) * 10 as decade
from tbl_people

and then use this intermediate result for counting SQL Fiddle

select count(*), decade, decade + 9
from (select floor(year(`year`) / 10) * 10 as decade
      from tbl_people) t
group by decade

or this SQL Fiddle, if you want the decade in one column

select count(*) as count, concat(decade, '-', decade + 9) as year
from (select floor(year(`year`) / 10) * 10 as decade
      from tbl_people) t
group by decade

Answer 2

This should work - SQL Fiddle (thanks to Olaf Dietsche for showing me that this wonderful site exists):

SELECT
    COUNT(`year`) as `count`,
    CONCAT(
        FLOOR(YEAR(`year`) / 10) * 10, 
        '-', 
        (CEIL(YEAR(`year`) / 10) * 10) - 1
    ) as `year`
FROM
    `tbl_people`
GROUP BY
    CONCAT(
        FLOOR(YEAR(`year`) / 10) * 10,
        '-', 
        (CEIL(YEAR(`year`) / 10) * 10) - 1
    )

If the number is 1992, FLOOR(1992 / 10) will give 199, and times with 10 will give 1990. The same thing about CEIL(1992 / 10) = 200 and times 10 = 2000, minus 1, gives 1999 so the year should be 1990-1999.

Answer 3

This will helps .

 select CONCAT(FLOOR(YEAR(from_unixtime(TIMESTAMP))/10)*10,'-',(FLOOR(YEAR(from_unixtime(TIMESTAMP))/10)*10)+9) as year , count(ID) from 
    TABLE group by FLOOR(YEAR(from_unixtime(TIMESTAMP))/10)*10;