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

Question

I have database table like this:

I want to display different 5-year age ranges and the counts of students that are in that range like below:

Here, the lowest age is 10 so we first calculate the range 10-15. There are 5 students within that range. For the second range, we need to find the age>15 which is 18. So, the second range is from 18-23, and so on. I would appreciate any help where the range is automatically calculated and count the data within that range.

Answer 1

You can use a condition inside of a SUM() statement to get a count where that condition holds. I would count the conditions where the age is BETWEEN() the necessary range. Try this:

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 only return one row, but it will have everything you need. Here is an SQL Fiddle example.

EDIT I misunderstood your question to automatically calculate the various ranges. I will leave my previous answer here because it may be beneficial to future readers looking for hard coded ranges. To do this, you'll have to set up a variable. I made a sort of running total type approach to get the groups. I started by setting @a to 0 before the query. Then, I needed to get two values:

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

I did this by changing the value of @a as necessary:

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

Then, I included these in a CONCAT() block and casted these values as chars in order to get the groups that I needed. It 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 with the age ranges you expect. I had to add the where clause because otherwise I would get one result row for each row in the table, which would give us several null rows.

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

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 beautifully in SQL Fiddle. Completely dynamic and returns the 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