Mysql GROUP BY context

Encountered this MYSQL challenge at work today.

Given a table called test_table like:

+—-+——-+
| id | value |
+—-+——-+
|  1 | 1     |
|  2 | 1     |
|  3 | 2     |
|  4 | 2     |
|  5 | 1     |
|  6 | 1     |
|  7 | 2     |
|  8 | 3     |
|  9 | 3     |
|  10 | 3     |
+—-+——-+

We wanted a result like:

+——-+——–+
| value | amount |
+——-+——–+
| 1     |      2 |
| 2     |      2 |
| 1     |      2 |
| 2     |      1 |
| 3     |      3 |
+——-+——–+

So basically, we wanted to keep the order, but GROUP BY when there appeared to be values that could be grouped together. So if the same value occured more than once they should be group together as one group without grouping every occurance of the value.

The usecase of this is typically a feed of events, like this:

• The event with of type 1 occured 2 times.
• The event with of type 2 occured 2 times.
• The event with of type 1 occured 2 times.
• The event with of type 2 occured 1 time.
• The event with of type 3 occured 3 times.

Instead of:

• The event with of type 1 occured 1 time.
• The event with of type 1 occured 1 time.
• The event with of type 2 occured 1 time.
• The event with of type 2 occured 1 time.
• The event with of type 1 occured 1 time.
• etc.

Took some work, but ended on this query:

SET @next := 0, @last := 0;

SELECT test_table.value,
COUNT(test_table.id) AS amount
FROM test_table
LEFT JOIN (
SELECT test_table.*,
IF(@last != value, @next := @next+100, 0) AS xx,
IF(@last != value, @last := value, 0) AS xy,
IF(value, value+@next, 0) as group_value
FROM test_table
) AS test_table_b ON test_table_b.id = test_table.id
GROUP BY test_table_b.group_value

Now, this is not all fool proof. The value that is added to @next may be a constant that has to change depending on the value you want to group by. The last issue to consider is that working with variables like this is not an exact sience, but it work in my example.

Hopefully this was helpfull to some. It was to me.