How Can I Use SQL's GROUP BY Clause to Aggregate Data into Ranges?

SQL GROUP BY for Data Range Aggregation

Analyzing data distribution across various ranges is a crucial aspect of data analysis. SQL's GROUP BY clause offers a robust mechanism for aggregating data into predefined groups, facilitating the generation of concise summary tables and reports.

Creating Range-Based Groups

To categorize data into specific ranges, we leverage the CASE WHEN statement to assign values to a new column based on defined conditions. This newly created column then serves as the basis for grouping data using the GROUP BY clause.

Illustrative Example: Score Range Grouping

Let's consider a scenario where we need to determine the frequency of scores within specific intervals (e.g., 0-9, 10-19, 20-29). The following query constructs a new column representing these score 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'
  END AS score_range
FROM scores;</code>

This query generates a score_range column, assigning each score to its corresponding range.

Aggregation with GROUP BY

To calculate the count of scores within each range, we employ the COUNT(*) aggregate function in conjunction with the GROUP BY clause. The query below achieves this:

<code class="language-sql">SELECT
  score_range,
  COUNT(*) AS score_count
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 score_range
  FROM scores
) AS ranged_scores
GROUP BY
  score_range;</code>

Output

The resulting table displays two columns: score_range and score_count, providing a clear overview of score distribution across the defined ranges.

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