How Can I Group Numerical Data into Ranges in SQL?

SQL Techniques for Binning Numerical Data

This guide demonstrates how to categorize numerical data into defined ranges within an SQL database. Imagine you have a table with a numerical column (e.g., "score"). The goal is to generate a summary showing the count of scores falling within each range.

Illustrative Example:

A summary table might look like this:

Score Range	Frequency
0-9	11
10-19	14
20-29	3
...	...

Here's how to achieve this in SQL Server 2000 and beyond:

Method 1: CASE Expression within a Subquery

This method uses a CASE expression inside a subquery to assign range labels to each score, followed by an outer query to count occurrences within each range.

<code class="language-sql">SELECT t.range AS "Score Range", COUNT(*) AS "Frequency"
FROM (
  SELECT score,
         CASE  
           WHEN score BETWEEN 0 AND 9 THEN '0-9'
           WHEN score BETWEEN 10 AND 19 THEN '10-19'
           ELSE '20-99'  -- Handle scores outside defined ranges
         END AS range
  FROM scores
) t
GROUP BY t.range;</code>

Method 2: CASE Expression in the Main Query

Alternatively, the CASE expression can be directly embedded in the main query, simplifying the structure slightly.

<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'  -- Handle scores outside defined ranges
    END AS "Score Range",
    COUNT(*) AS "Frequency"
FROM scores
GROUP BY 
    CASE  
        WHEN score BETWEEN 0 AND 9 THEN '0-9'
        WHEN score BETWEEN 10 AND 19 THEN '10-19'
        ELSE '20-99'
    END;</code>

These methods efficiently group and summarize data into custom ranges, offering valuable insights into data distribution. Remember to adjust the CASE statements to match your specific score ranges. Consider adding error handling for values outside the defined ranges.

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