SQL sum results by type

bakis 0
Viewed 36

I am wondering if there is a way to group the results sequentially by type until the different types were reached. It is hard to explain using words so maybe an example below will help.

  • Sum the first three rows because they all have "buy" type;
  • The system reaches different type, so now it sums all the "sell" type records until we reach "buy" type again.
id type quantity
1 buy 2
2 buy 5
3 buy 3
4 sell 4
5 buy 3
6 sell 1
7 sell 1

Final result:

id type quantity
3 buy 10
4 sell 4
5 buy 3
7 sell 2

The id of records does not matter, I care only about the type and quantity.

mysql sql
1 Answers

First we mark every time there's a change in type using lag and ordering by id, and then we count to create distinct groups.

select    type
         ,sum(quantity) as quantity
from     (
          select  *
                  ,count(chng) over(order by id) as grp
          from   (
                  select *
                         ,case when type <> lag(type) over(order by id) then 1 end as chng
                  from   t
                 ) t
         ) t
group by grp, type
type quantity
buy 10
sell 4
buy 3
sell 2

Fiddle

DannySlor0
Related