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;
精彩评论