In Postgres SQL how to convert values as range and get the range with maximum records-CodePudding

Having a table of students with their name and age as follows how to convert values of age as a range of

7-9
9-11
11-13
13-15
15-17
17-19

and get the age range with maximum students

Creating table:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age FLOAT NOT NULL
);

Inserting values:

INSERT INTO students
VALUES
(1, 'Ryan', 12),
(2, 'Joanna', 12.5),
(3, 'James', 11),
(4, 'Karen', 10),
(5, 'Holmes', 11.2),
(6, 'Garry', 12.1),
(7, 'Justin', 14.5),
(8, 'Emma', 15),
(9, 'Andy', 10),
(10, 'Claren', 9.5),
(11, 'Dennis', 9),
(12, 'Henna', 16),
(13, 'Iwanka', 15.4),
(14, 'June', 8.1),
(15, 'Kamila', 7.5),
(16, 'Lance', 17);

Expected Output should be range with max count of records:

Range | Count 
10-12 | 5

CodePudding user response：

--construct numrange table.

create table age_range(
id serial,
agerange numrange);
insert into age_range(agerange) values
('[7,9]'),('[10,12]'),('[13,15]'),
('[15,17]'),('[17,19]');

--cte with window function.

with a as(
select age, name,  agerange
from students s, age_range a
where age<@ agerange is true)
select *, count(agerange) over (partition by agerange)
from a order by agerange,name;

CodePudding user response：

You can try to use an aggregate function with CASE WHEN expression for your logic, then use ORDER BY COUNT DESC to get max count of records

SELECT (CASE WHEN age BETWEEN 7 AND 9 THEN '7-9'
           WHEN age BETWEEN 10 AND 12 THEN '10-12'
           WHEN age BETWEEN 13 AND 15 THEN '13-15'
           WHEN age BETWEEN 15 AND 17 THEN '15-17'
           WHEN age BETWEEN 17 AND 19 THEN '17-19' END) as range,
      COUNT(*) cnt
FROM students
GROUP BY  CASE WHEN age BETWEEN 7 AND 9 THEN '7-9'
           WHEN age BETWEEN 10 AND 12 THEN '10-12'
           WHEN age BETWEEN 13 AND 15 THEN '13-15'
           WHEN age BETWEEN 15 AND 17 THEN '15-17'
           WHEN age BETWEEN 17 AND 19 THEN '17-19' END
ORDER BY COUNT(*) DESC 
LIMIT 1

edit

if you range number has a logic and you want a generic range solution

you can try to use generate_series generate a range number with your range logic then do outer join.

For your sample data I would use generate_series(7,17,2) create a range number which you expect the calutaion start and end number

SELECT CONCAT(t1.startnum,'-',t1.endnum) as range,
      COUNT(*) cnt
FROM students s
INNER JOIN (
    SELECT v startnum,v 2 endnum
    FROM generate_series(7,17,2) v 
  ) t1 ON s.age BETWEEN t1.startnum AND t1.endnum
GROUP BY CONCAT(t1.startnum,'-',t1.endnum)
ORDER BY COUNT(*) DESC 
LIMIT 1

sqlfiddle