How to Group Data by Ranges Using SQL's GROUP BY Clause?

Group data by range using SQL's GROUP BY clause

The "GROUP BY" clause in SQL allows us to aggregate data into groups based on specified columns. When working with numeric data, it is useful to group values ​​into ranges for analysis and reporting.

Question:

How to "group" by range in SQL?

Answer:

To group values ​​by range, you can use the following syntax:

<code class="language-sql">SELECT
  CASE
    WHEN value BETWEEN start AND end THEN 'range_name'
    -- 为其他范围添加更多情况
  END AS range,
  COUNT(*) AS count
FROM table_name
GROUP BY range;</code>

Instructions:

• CASE statement classifies each value into a range.
• BETWEEN operator checks whether the value falls within the specified range.
• The GROUP BY clause groups results by range values.
• COUNT(*) function counts the number of occurrences in each range.

Example:

Consider the following example of grouping scores into ranges:

<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' -- 处理大于或等于20的分数
  END AS score_range,
  COUNT(*) AS count
FROM scores
GROUP BY score_range;</code>

This query will produce the following output:

score_range	count
0-9	11
10-19	14
20-99	3

The above is the detailed content of How to Group Data by Ranges Using SQL's GROUP BY Clause?. For more information, please follow other related articles on the PHP Chinese website!