mysql - Sum the values of a column based on a group of values from another column

Question

I have a table where each transaction is associated to a given price. Something like this:

transaction  price  
     1        10  
     2        20  
     3        30  
     5        50  
     6        10  
    10        10  
    23        10  
    24        10  
    25        10  
    26        10  
    27        10  

I'm trying to find a way to sum the price based on a specific transaction or for a group of transactions. The result of the query would be something like this:

transaction  price
   1-3        60
   5-6        60
    10        10
 23-27        50

This way I can tell that for the transactions 1 to 3 the result was 60, and so on. Can you point me in the right direction to make this using MySQL?

Answer 1

Since mysql doesn't have support for windowing functions, we have to create our own group ranking for your table, and then another query to operate on the results.

select if(count(transaction) = 1, transaction, concat(min(transaction), '-', max(transaction))) transactions, sum(price) price 
  from (
    select if(`transaction` = @prev + 1, 
         if(@prev := `transaction`, @rank, @rank),
           if(@prev := `transaction`, @rank := @rank + 1, @rank := @rank + 1)
       ) gr,
       `transaction`,
       price
    from table1, (select @rank := 1, @prev := 0) q
    order by `transaction` asc
  ) q     
  group by gr

demo here