I have a table where each
transaction is associated to a given
price. Something like this:
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:
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?
Best How To :
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
select if(`transaction` = @prev + 1,
if(@prev := `transaction`, @rank, @rank),
if(@prev := `transaction`, @rank := @rank + 1, @rank := @rank + 1)
from table1, (select @rank := 1, @prev := 0) q
order by `transaction` asc
group by gr