如何使用BETWEEN、COUNT与ALIAS统计同一列的数值区间（如10-19等）

Let’s walk through this with a practical example—say you have a student_scores table with a score column, and you want to tally how many students fall into ranges like 10-19, 20-29, etc. Here’s exactly how to do it:

Core Solution: CASE + COUNT + Aliases #

The simplest approach uses a CASE expression to map each value to its interval, then COUNT() to tally groups, with aliases for readability.

Example Query #

SELECT
  CASE
    WHEN score BETWEEN 10 AND 19 THEN '10-19'
    WHEN score BETWEEN 20 AND 29 THEN '20-29'
    WHEN score BETWEEN 30 AND 39 THEN '30-39'
    WHEN score >= 40 THEN '40+'
    ELSE '<10' -- Catch-all for scores below 10
  END AS score_interval,
  COUNT(*) AS total_students
FROM student_scores
GROUP BY score_interval
ORDER BY score_interval;

Key Breakdown: #

• CASE Expression: Uses BETWEEN to cleanly define inclusive ranges (remember, BETWEEN a AND b equals >=a AND <=b). Each matching value gets assigned to a human-readable interval string.
• Alias (AS score_interval): Renames the computed interval column so your results don’t show a messy default name like CASE WHEN ....
• COUNT()*: Counts every row in each interval group. Aliasing this as total_students makes the count’s purpose immediately clear.
• GROUP BY: Groups results by our computed interval column, ensuring one row per range.
• ORDER BY: Keeps intervals in logical order (avoids alphabetical chaos where '40+' would come before '10-19').

Simplify for Equal-Sized Intervals #

If you’re working with consistent, evenly spaced ranges (like 10-point increments), you can skip writing a long CASE statement with integer division:

SELECT
  CONCAT(FLOOR(score / 10) * 10, '-', FLOOR(score / 10) * 10 + 9) AS score_interval,
  COUNT(*) AS total_students
FROM student_scores
WHERE score >= 10 AND score < 40 -- Filter to your target range
GROUP BY FLOOR(score / 10)
ORDER BY FLOOR(score / 10);

This dynamically generates interval labels (e.g., 10-19, 20-29) without hardcoding each range—perfect for large datasets.

Sample Output #

For a table with scores: 15, 22, 28, 35, 12, 45, 9, the first query returns:

内容的提问来源于stack exchange，提问作者Oluseye Ademola