How to group data based on appearance, into pre set buckets in MYSQL

Question

pic 1

hi, How can i group data based on appearance, into pre set buckets in MYSQL? lets say my appearances are like in picture 1, and i need the output as seen in picture 2? (the ranges values are pre set) thanks in advance

,pic 2

pic 3

Answer 1

You can solve this via a second table which defines the ranges and then use a join on a GROUP BY query. The steps will be as follow:

1. Create a new table with the ranges
2. Develop a query for counting and grouping the original data
3. Run a JOIN query against the new ranges table to determine in which bucket the count will be placed.

Creating and filling the new table for the ranges will be easy (try to avoid the name "range", as it is a keyword):

CREATE TABLE sections
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    start INT,
    end INT
);

Fill it with your sections :

INSERT INTO sections(name, start, end) VALUES ('0-2', 0, 2);
[...]
INSERT INTO sections(name, start, end) VALUES ('11+', 11, 1<<31);

Create a query to group and count your data. This example use a table called 'events' with the columns 'catId' and 'timestamp':

SELECT
    COUNT(*) as eventCount
FROM
    events
GROUP BY
    catId

This might create a result like this (there are only 5 different cat ids used):

+------------+
| eventCount |
+------------+
|          3 |
|          3 |
|          5 |
|          1 |
|          1 |
+------------+

Now you need to JOIN this result to the sections table by using the ON condition. For teaching purposes an intermediate (but not working) query will look like this:

SELECT
    s.id,
    s.name,
    s.start,
    s.end,
    COUNT(*) AS sectionCount
FROM
    sections s
JOIN
    (__your_previous_result_here__) e
ON
    e.eventCount BETWEEN s.start AND s.end
GROUP BY
    s.id

Your total query will look like this:

SELECT
    s.id,
    s.name,
    s.start,
    s.end,
    COUNT(*) AS sectionCount
FROM
    sections s
JOIN
    (SELECT
         COUNT(*) as eventCount
     FROM
         events
     GROUP BY
         catId) e
ON
    e.eventCount BETWEEN s.start AND s.end
GROUP BY
    s.id

Please notice the aliases s and e being used here. Depending on the input data the result will look like this:

+----+------+-------+------------+--------------+
| id | name | start | end        | sectionCount |
+----+------+-------+------------+--------------+
|  1 | 0-1  |     0 |          1 |            2 |
|  2 | 2-4  |     2 |          4 |            2 |
|  3 | 5++  |     5 | 2147483647 |            1 |
+----+------+-------+------------+--------------+