开发者

Select number groups in query

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

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

The score goes from 3 开发者_如何学JAVAto 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!


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 


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


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


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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜