Display dynamic ranges from a database table and count rows in each range

Question

I have a database table as follows:

I want to display different 5-year age ranges and the number of students who are in this range, as shown below:

Here the lowest age is 10, so we will first calculate the range of 10-15. There are 5 students in this range. For the second range, we need to find an age> 15, which is equal to 18. Thus, the second range is from 18 to 23 and so on. I would appreciate any help when a range is automatically calculated and counts data in that range.

Answer 1

You can use the condition inside the SUM () operator to get the counter at which this condition is satisfied. I would consider conditions when the age of BETWEEN () is the required range. Try the following:

SELECT SUM(age BETWEEN 10 AND 15) AS '10-15', SUM(age BETWEEN 18 AND 23) AS '18-23', SUM(age BETWEEN 26 AND 31) AS '26-31', SUM(age BETWEEN 34 AND 39) AS '34-39' FROM myTable; 

This will return only one row, but it will have everything you need. The following is an example of an SQL Fiddle .

EDIT I misunderstood your question in order to automatically calculate the various ranges. I will leave my previous answer here because it may be useful for future readers looking for hard-coded ranges. To do this, you will need to configure the variable. I did a kind of full-type approach to get groups. I started by setting @a to 0 before requesting. Then I needed to get two values:

• The minimum age from the table, where age> @a
• 5 is greater than this variable.

I did this by changing the @a value as needed:

• @a: = (SELECT MIN (age) FROM myTable WHERE age> = @a)
• @a: = @a + 5

Then I included them in the CONCAT() block and selected these values ​​as characters to get the groups that I need. This may look complicated, so I hope I explained the concept:

 SELECT CONCAT (CAST(@a := (SELECT MIN(age) FROM myTable WHERE age > @a) AS CHAR), ' - ', CAST((@a := @a + 5) AS CHAR)) AS ageRange FROM myTable WHERE @a <= (SELECT MAX(age) FROM myTable); 

Doing this gave me four rows, each of which corresponds to age ranges. I had to add a where clause, because otherwise I would get one row of result for each row in the table, which would give us some zero rows.

Finally, I included a subquery to get the number of students whose age is within the required range. Note that the first part changes @a values, so instead of checking from @a to @a + 5, I check from @ a-5 to @a. Here is the final request:

 SET @a = 0; SELECT CONCAT(CAST(@a := (SELECT MIN(age) FROM myTable WHERE age > @a) AS CHAR), ' - ', CAST((@a := @a + 5) AS CHAR)) AS ageRange, (SELECT COUNT(*) FROM myTable WHERE age BETWEEN @a - 5 AND @a) AS numStudents FROM myTable WHERE @a <= (SELECT MAX(age) FROM myTable) GROUP BY ageRange; 

It worked great in SQL Fiddle . Fully dynamic and returns various groups of 5 without any prior knowledge of which groups to take.

Answer 2

 SELECT CASE WHEN age>=10 AND age<=15 THEN '10-15' WHEN age>=18 AND age<=23 THEN '18-23' WHEN age>=26 AND age<=31 THEN '26-31' WHEN age>=34 AND age<=39 THEN '34-39' ELSE 'OTHER' END AS age_range, COUNT(*) as number_of_students FROM table GROUP BY age_range