CODEWITHSUNDEEP
sqlpostgresql
Dave Qorashi
Dave Qorashi

Reputation: 336

Group records into three predefined buckets in Postgres

Considering the following table, how can I group these scores into three buckets (not more): less than equal 150, between 150 and 350, more than 350.

 id | score
----+-------
  1 |     5
  2 |     5
  3 |     5
  4 |     4
  5 |     5
  6 |     4
  7 |     4
  8 |     4
  9 |     2
 10 |     2
 11 |     6
 12 |   205
 13 |   250
 13 |   400
 14 |   105
 15 |   900
 16 |   1300

I tried this method:

select (score/100)*100 || '-' || (score/100)*100 + 100 as scorerange,count(*)
from scores group by score/100 order by score/100;

And this is the result:

 scorerange | count
------------+-------
 0-100      |    11
 100-200    |     1
 200-300    |     2
 400-500    |     1
 900-1000   |     1
 1300-1400  |     1
(6 rows)

It groups the scores but not in the three buckets that I need.

Upvotes: 0

Views: 429

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657882

Group by a CASE statement, but make sure to get the bounds right:

SELECT CASE
          WHEN score <= 150 THEN '0-150'
          WHEN score <= 350 THEN '151-350'
          ELSE                   '351+'
       END AS score_range
     , count(*) AS count
FROM   scores
GROUP  BY 1
ORDER  BY 1;  -- happens to work correctly with the text value.

Assuming the column score is defined NOT NULL and values are positive.

Upvotes: 2

edruid
edruid

Reputation: 703

This will give you the ranges you requested:

SELECT 
    CASE WHEN min(score) <= 150 THEN '*-150'
         WHEN min(score) <= 350 THEN '151-350'
         ELSE '350-*'
         END AS scorerange, #A comma is needed here right after scorerange
    count(*)
FROM scores
GROUP BY score <= 150, score <= 350;

Upvotes: 1

Arun
Arun

Reputation: 1

This Query will give you the expected Output.

SELECT CASE WHEN Score <= 150 THEN '0-150'
         WHEN Score >= 350 THEN '150-350'
         ELSE '350+' END AS SCORE_RANGE,             
         COUNT(*) AS COUNT
    FROM SCORES
    GROUP BY SCORE_RANGE

Upvotes: 0

Related Questions