The query displays the age group of users from 1900 ... 2007. Output occurs by year.
It is necessary to make at the same time grouping by years and within certain age groups, up to 12, 12 - 18, 18 - 21, 21+.
I hope the scheme is clearly outlined. Not the essence of what framework, I need to make the main grouping on the framework of years.
Everything almost works, you just need to add GROUP BY.

VKontakte example VKontakte example

SELECT DATE_FORMAT(`date_rozdenie`, '%d.%m.%Y') AS `year`, SUM(`activation`) AS `active` FROM `users` WHERE DATE_FORMAT(`date_rozdenie`, '%Y') >= (YEAR(NOW()) - 100) AND DATE_FORMAT(`date_rozdenie`, '%Y') <= (YEAR(NOW()) - 8) GROUP BY DATE_FORMAT(`date_rozdenie`, '%Y') ORDER BY DATE_FORMAT(`date_rozdenie`, '%Y')

Output data for this query. Date of birth and how many people are with this age. enter image description here

  • So what's the problem, where is php? i only see js and not a single render code for your framework not in php, not in js not html. - Naumov
  • specify a question on drawing data graphics or writing a query for data collection? - Igor Karpenko
  • Everything is ready for me already, only it is necessary to derive data from the database within the framework of years, the screen is only as an indicator of what I am talking about. This is from VK. I have about the same schedule. GROUP BY needs to be added as something, but I don’t understand how ( - Albert Ushakov

2 answers 2

Use case grouping. Such a scheme

CASE WHEN <date of birth> BETWEEN ... AND ... THEN 'from 18 to 21' WHEN ... etc.

  • If it's not difficult for you, of course, can you show it on my code? I would be very grateful, so I would understand what was happening. - Albert Ushakov

Here is the finished code.

 SELECT CASE WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 13) AND (YEAR(NOW())) THEN ' до 13 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 18) AND (YEAR(NOW()) - 14) THEN ' от 14 до 18 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 23) AND (YEAR(NOW()) - 19) THEN ' от 19 до 23' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 28) AND (YEAR(NOW()) - 24) THEN ' от 24 до 28 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 33) AND (YEAR(NOW()) - 29) THEN ' от 29 до 33 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 45) AND (YEAR(NOW()) - 34) THEN ' от 34 до 45' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 100) AND (YEAR(NOW()) - 46) THEN ' от 46 ' END `date_rozdenie`, SUM(`activation`) AS `active` FROM `users` WHERE DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 100) AND (YEAR(NOW()) - 8) GROUP BY CASE WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 13) AND (YEAR(NOW())) THEN ' до 13 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 18) AND (YEAR(NOW()) - 14) THEN ' от 14 до 18 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 23) AND (YEAR(NOW()) - 19) THEN ' от 19 до 23' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 28) AND (YEAR(NOW()) - 24) THEN ' от 24 до 28 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 33) AND (YEAR(NOW()) - 29) THEN ' от 29 до 33 ' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 45) AND (YEAR(NOW()) - 34) THEN ' от 34 до 45' WHEN DATE_FORMAT(`date_rozdenie`, '%Y') BETWEEN (YEAR(NOW()) - 100) AND (YEAR(NOW()) - 46) THEN ' от 46 ' END ORDER BY DATE_FORMAT(`date_rozdenie`, '%Y')