CODEWITHSUNDEEP
mysqlsql
Hamed Kamrava
Hamed Kamrava

Reputation: 12867

How to GROUP BY on a column with considering record sequences?

Sorry for my bad English.

I'm trying to GROUP BY on bunch of user_id's with considering its sequences. I'll try to explain you what exactly I want :

table: orders

+-----------+----------+------------+
|    id     | user_id  |   amount   |
+-----------+----------+------------+
|   1       | 100      |     5      |
|   2       | 100      |     5      |
|   3       | 100      |     10     |
|   4       | 101      |     15     |
|   5       | 101      |     10     |
|   6       | 101      |     5      |
|   7       | 102      |     5      |
|   8       | 100      |     5      |
|   9       | 100      |     10     |
|   10      | 102      |     10     |
+-----------+----------+------------+

What I want is :

+-----------+----------+------------+----------------+
| user_id   |   count  |  total_amount  |  id_range  |
+-----------+----------+------------+----------------+
|   100     |    3     |       20       |     1,3    |
|   101     |    3     |       30       |     4,6    |
|   102     |    1     |       5        |     7,7    |
|   100     |    2     |       15       |     8,9    |
|   102     |    1     |       10       |    10,10   |
+-----------+----------+------------+----------------+

What I've tried so far :

SELECT user_id, count(user_id) as count, sum(amount) as total_amount FROM `orders` GROUP By user_id

But it just GROUP BY on all records, regardless of its sequences.

Upvotes: 3

Views: 48

Answers (1)

fthiella
fthiella

Reputation: 49079

MySQL doesn't have a rank or row number function, and this makes the answer a little more complicated. For using a ranking function see this question and answers:

http://stackoverflow.com/questions/1895110/row-number-in-mysql

And one possible query is this:

SELECT
  user_id,
  count(*) as cnt,
  sum(amount) as total_amount,
  CONCAT_WS(',', min(id), max(id)) AS id_range
FROM (
SELECT
  id,
  id - 
  CASE
    WHEN @prev_value = user_id THEN @rank_count := @rank_count + 1
    WHEN @prev_value := user_id THEN @rank_count
  END AS grp,
  user_id,
  amount
FROM
  orders CROSS JOIN (SELECT @prev_value:=NULL, @rank_count:=0) r
ORDER BY
  id
) s
GROUP BY grp

Please see this fiddle.

Upvotes: 2

Related Questions