How to Group Data by Ranges and Count Occurrences in SQL?

Group data by range in SQL

In SQL, creating a count table that displays the number of occurrences of a value in a specific range is a common data manipulation task. This can be achieved by using a combination of GROUP BY clauses and range classification techniques.

Suppose you have a table with a numeric column called "score". To generate a table showing score counts within a specified range, follow these steps:

1. Create a temporary table or derived table to classify each score into its corresponding range. This can be done using CASE expressions to evaluate fractional values ​​and assign them to range categories.

   For example, the following example creates a temporary table t on a SQL Server 2000 database that assigns scores to ranges:

   <code class="language-sql">select t.range as [score range], count(*) as [number of occurrences]
   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>

2. Group temporary tables by range categories and count occurrences within each range.

   The GROUP BY clause in the above query groups the rows in derived table t by range category and then applies the COUNT(*) function to determine the number of occurrences of each range.

By following these steps, you can effectively group data in SQL by range and get a table that shows the count of values ​​in each specified range.

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