Geek Answers Handbook

Group ranks by range of ranges by year

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


I can write multiple queries for this, but that means I have to write different queries for each range. Is there a way to dynamically increase from the very last year (10-year intervals) and count the number of records within the intervals?

+4
source share
3 answers

First calculate the decade for each row

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

and then use this intermediate result to count SQL Fiddle

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

or SQL Fiddle if you want a 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
+4
source

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 a time from 10 will give 1990 . The same thing in CEIL(1992 / 10) = 200 and times 10 = 2000 , minus 1 , gives 1999 , so the year should be 1990-1999 .

0
source

This will help.

  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;
0
source

More articles:


All Articles