How to Group Data into Ranges using SQL?

SQL data range grouping

Group data into different ranges is a common task in data analysis. There are several ways to do this in SQL, including using CASE expressions and conditional statements.

Method 1: Use CASE expression

Using a CASE expression, you can create a new column called "range" to assign each score to a range. For example:

<code class="language-sql">SELECT 
  CASE 
    WHEN score BETWEEN 0 AND 9 THEN '0-9'
    WHEN score BETWEEN 10 AND 19 THEN '10-19'
    ELSE '20-99' 
  END AS range
FROM scores;</code>

You can then use this range column to group and calculate the result:

<code class="language-sql">SELECT range AS [分数范围], COUNT(*) AS [出现次数]
FROM (
  SELECT CASE 
    WHEN score BETWEEN 0 AND 9 THEN '0-9'
    WHEN score BETWEEN 10 AND 19 THEN '10-19'
    ELSE '20-99' 
  END AS range
  FROM scores) t
GROUP BY t.range;</code>

Method 2: Use conditional statements in the SELECT statement

Alternatively, you can create the range column directly using a conditional statement in the SELECT clause:

<code class="language-sql">SELECT 
  CASE 
    WHEN score >= 0 AND score < 10 THEN '0-9'
    WHEN score >= 10 AND score < 20 THEN '10-19'
    ELSE '20-99' 
  END AS [分数范围], COUNT(*) AS [出现次数]
FROM scores
GROUP BY [分数范围];</code>

Both methods produce the desired output, showing the number of occurrences for each score range.

The above is the detailed content of How to Group Data into Ranges using SQL?. For more information, please follow other related articles on the PHP Chinese website!