Grouping by Range Intervals

In this lesson, you will learn how to transform continuous numerical data into meaningful "buckets" to create histogram-style reports.

The Concept: From Continuous to Discrete

In many databases, you deal with raw, continuous values like customer ages, transaction amounts, or test scores. If you perform a GROUP BY on raw values, you’ll likely end up with thousands of individual rows. To make this data actionable, we use Grouping by Range Intervals to categorize these values into discrete bands (e.g., 0-10, 11-20, 21-30).

The Mathematical Approach: Integer Division

The most efficient way to group by fixed ranges is using integer division. By dividing your value by your desired bucket size, the remainder is truncated, effectively "snapping" the value into a specific interval.

For example, if you want to group age into 10-year brackets:

1. Divide the age by 10.
2. Multiply the result back by 10.
3. This creates a "floor" value for each range.

SELECT 
    (age / 10) * 10 AS age_range_start,
    COUNT(*) AS customer_count
FROM customers
GROUP BY (age / 10) * 10
ORDER BY age_range_start;

Advanced Control with CASE

While integer division is perfect for uniform ranges, what if your business logic requires irregular intervals (e.g., "Junior" 0-18, "Adult" 19-64, "Senior" 65+)? For this, we use conditional logic within the GROUP BY clause. By defining the boundaries explicitly, we can categorize data based on specific business rules rather than fixed mathematical intervals.

SELECT 
    CASE 
        WHEN age < 19 THEN 'Junior'
        WHEN age BETWEEN 19 AND 64 THEN 'Adult'
        ELSE 'Senior'
    END AS age_bracket,
    COUNT(*) AS total
FROM customers
GROUP BY 
    CASE 
        WHEN age < 19 THEN 'Junior'
        WHEN age BETWEEN 19 AND 64 THEN 'Adult'
        ELSE 'Senior'
    END;

By grouping these ranges, you turn granular noise into clear, visual trends, allowing you to identify distributions—the foundation of any histogram analysis.

Key Takeaway: Use integer division for uniform buckets to simplify code, or use a CASE statement for custom, business-defined intervals to categorize continuous data into meaningful segments.