Select number groups in query

Question

I have a table where I keep users numbers and their "score"

user_number | score
  0832824       6
  0478233       3
    ...        ...

The score goes from 3 to 15. I want to create a query that would fetch the score and the total of users with this score. But I need to make four different groups of score : 12 to 15, 8 to 11, 5 to 7 and less than 5.

Sort of like this :

  score  | total_users
12 to 15      5000
 8 to 11      3000
   ...         ...

Thanks for any answer you can provide!

See this: http://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges — ypercubeᵀᴹ, May 12 '11 at 13:33

score 2 · Accepted Answer · edited May 12 '11 at 13:39

2

SELECT t.range AS score, COUNT(*) AS total_users from 
(   
    SELECT CASE WHEN score BETWEEN 12 AND 15 THEN '12 to 15'     
                WHEN score BETWEEN 8 AND 11 THEN '8 to 11' 
                WHEN score BETWEEN 5 AND 7 THEN '5 to 7'    
                WHEN score < 5 THEN 'less than 5' 
           END AS range   
    FROM scores) t 
GROUP BY t.range

edited May 12 '11 at 13:39

ypercubeᵀᴹ

113,259
19
174
235

answered May 12 '11 at 13:34

anothershrubery

20,461
14
53
98

score 1 · Answer 2 · answered May 12 '11 at 13:32

You could select each group individually using a BETWEEN clause and combine the results using a UNION

SELECT  score = '12 to 15', total_users = COUNT(*)
FROM    ATable
WHERE   score BETWEEN 12 AND 15
UNION ALL
SELECT  score = '8 to 11', total_users = COUNT(*)
FROM    ATable
WHERE   score BETWEEN 8 AND 11
UNION ALL
SELECT  score = '5 to 7', total_users = COUNT(*)
FROM    ATable
WHERE   score BETWEEN 5 AND 7
UNION ALL
SELECT  score = 'less than 5', total_users = COUNT(*)
FROM    ATable
WHERE   score < 5

score 1 · Answer 3 · answered May 12 '11 at 13:33

SELECT SUM(CASE WHEN score BETWEEN 12 AND 15 THEN 1 ELSE 0 END) AS [12-15],
       SUM(CASE WHEN score BETWEEN 8 AND 11 THEN 1 ELSE 0 END) AS [8-11],
       SUM(CASE WHEN score BETWEEN 5 AND 7 THEN 1 ELSE 0 END) AS [5-7],
       SUM(CASE WHEN score < 5 THEN 1 ELSE 0 END) AS [<5]
    FROM YourTable

score 0 · Answer 4 · answered May 12 '11 at 13:36

CREATE TABLE scores
(
   score         NUMBER,
   user_number   VARCHAR2 (20)
);

INSERT INTO scores
     VALUES (3, '000001');

INSERT INTO scores
     VALUES (4, '000002');

INSERT INTO scores
     VALUES (4, '000003');

INSERT INTO scores
     VALUES (12, '000005');

COMMIT;

  SELECT score, COUNT (DISTINCT user_number) number_of_users
    FROM (SELECT CASE
                    WHEN score < 5 THEN 'Below 5'
                    WHEN score >= 5 AND score <= 7 THEN '5 to 7'
                    WHEN score >= 8 AND score <= 11 THEN '8 to 11'
                    WHEN score >= 12 AND score <= 15 THEN '12 to 15'
                    ELSE 'unclassified'
                 END
                    score,
                 user_number
            FROM scores)
GROUP BY score;

Select number groups in query

4 Answers4