CODEWITHSUNDEEP
sqldatabasepostgresql
Vinit Khandelwal
Vinit Khandelwal

Reputation: 627

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

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

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

Upvotes: 1

Views: 645

Answers (2)

jian
jian

Reputation: 4824

--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 b
    WHERE
        age <@ agerange IS TRUE
)
SELECT
    *,
    count(agerange) OVER (PARTITION BY agerange)
FROM
    a
ORDER BY
    agerange,
    name;

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions