SQL Data Quantization: Group Values by Range in MySQL

Let's say we have a payment table with the following structure:

CREATE TABLE payment (
    id_user INT,    
    sum INT,        
    date DATE       
);

The Task

We need to group payment data by amount ranges with a quantum of 50 rubles. For example, we want to see how many users paid:

• 0-49 rubles
• 50-99 rubles
• 100-149 rubles And so on up to 100,000 rubles.

The Solution

Here's an elegant SQL query that solves this task:

SELECT 
    COUNT(id_user),
    CONCAT_WS('...', 
        FLOOR(sum/50)*50, 
        (FLOOR(sum/50)+1)*50-1
    ) AS interval_range
FROM payment
GROUP BY FLOOR(sum/50);

How it Works

1. FLOOR(sum/50) divides each payment amount by 50 and rounds down, creating quantum groups
2. FLOOR(sum/50)*50 gives us the lower bound of each range
3. (FLOOR(sum/50)+1)*50-1 calculates the upper bound
4. CONCAT_WS combines these bounds with '...' to create readable ranges
5. Finally, we group by the quantum calculation to count users in each range

The output will look something like this:

count | interval_range
14    | 0...49
59    | 50...99
147   | 100...149

This query efficiently groups payments into 50-ruble ranges, making it easy to analyze payment distribution patterns.

Thanks for reading! If you found this helpful, please subscribe to my Telegram channel for more development tips and tricks: @asanovpro