PostgreSQL: How to Count Values by Range

Often in PostgreSQL you may want to count values based on ranges.

You can use a CASE WHEN statement with the following syntax to do so:

SELECT
      CASE WHEN points >= 10 AND points <= 19 THEN '10 - 19'
           WHEN points > 19 AND points <= 29 THEN '20 - 29'
           WHEN points > 29 AND points <= 39 THEN '30 - 39'
      END points_buckets,
      COUNT(*) AS range_count
   FROM
      athletes
   GROUP BY 1
   ORDER BY points_buckets;

This particular example will count the occurrences of values in the points column of the table named athletes based on the following ranges of values in the points column:

• 10 – 19
• 20 – 29
• 30 – 39

The following example shows how to use this syntax in practice.

Example: How to Count Values by Range in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points INT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 35);
INSERT INTO athletes VALUES (0003, 'Mavs', 22);
INSERT INTO athletes VALUES (0004, 'Mavs', 18);
INSERT INTO athletes VALUES (0005, 'Hawks', 19);
INSERT INTO athletes VALUES (0006, 'Hawks', 15);
INSERT INTO athletes VALUES (0007, 'Hawks', 17);
INSERT INTO athletes VALUES (0008, 'Hawks', 24);

-- view all rows in table
SELECT * FROM athletes;

 athleteid | team  | points 
-----------+-------+--------
         1 | Mavs  |     15
         2 | Mavs  |     35
         3 | Mavs  |     22
         4 | Mavs  |     18
         5 | Hawks |     19
         6 | Hawks |     15
         7 | Hawks |     17
         8 | Hawks |     24

Suppose that we would like to count the occurrences of values in the points column based on the following ranges of values:

• 10 – 19
• 20 – 29
• 30 – 39

We can use the following syntax to do so:

SELECT
      CASE WHEN points >= 10 AND points <= 19 THEN '10 - 19'
           WHEN points > 19 AND points <= 29 THEN '20 - 29'
           WHEN points > 29 AND points <= 39 THEN '30 - 39'
      END points_buckets,
      COUNT(*) AS range_count
   FROM
      athletes
   GROUP BY 1
   ORDER BY points_buckets;

 points_buckets | range_count 
----------------+-------------
 10 - 19        |           5
 20 - 29        |           2
 30 - 39        |           1

The resulting table shows the count of values based on the three ranges that we specified.

For example, we can see:

• A total of 5 values in the points column are between 10 and 19.
• A total of 2 values in the points column are between 20 and 29.
• A total of 1 value in the points column is between 30 and 39.

Feel free to use similar syntax to count values by ranges in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Random Value from Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty