CODEWITHSUNDEEP
sqldatabasegroup-bydb2
James McLeod
James McLeod

Reputation: 1

Grouping Consecutive Rows in DB2

I have a table in DB2 that looks like the following:-

ID        DESCRIPTION        TYPE    AMOUNT
--        -----------        ----    ------
1         Item 1             ONE     100
2         Item 2             ONE     50    
3         Item 3             TWO     100
4         Item 4             TWO     50
5         Item 5             ONE     100
6         Item 6             TWO     50
7         Item 7             ONE     100
8         Item 8             TWO     50
9         Item 9             TWO     100
10        Item 10            ONE     50

I want to group consecutive rows with the same type so it looks like the following:-

TYPE    AMOUNT
----    ------
ONE        150            -- (ID 1,2)
TWO        150            -- (ID 3,4)
ONE        100            -- (ID 5)
TWO        50             -- (ID 6)
ONE        100            -- (ID 7)
TWO        150            -- (ID 8,9)
ONE        50             -- (ID 10)

Note the last column is just to denote which Id's are in the rollup.

I'd rather do this with a query than a Stored Procedure.

Upvotes: 0

Views: 881

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13238

Think I got it:

Fiddle: 

with sub1 as
 (select x.*,
         case when chk is not null
              then row_number() over(order by id)
              else null
              end as rn
    from (select id,
                 description,
                 type,
                 amount,
                 case when lag(type, 1) over(order by id) <> type
                      then 'X'
                      else null
                      end as chk
            from tbl
           order by id) x)
select min(type),
       sum(amount) as amount,
       listagg(id, ', ') within group(order by id) as ids
  from (select z.id, z.description, z.type, z.amount, y.rn
          from sub1 z
         cross join sub1 y
         where z.rn is null
           and y.rn = (select max(x.rn)
                         from sub1 x
                        where x.id < z.id
                          and x.rn is not null)
        union all
        select id, description, type, amount, rn
          from sub1
         where rn is not null
        union all
        select id, description, type, amount, 1
          from sub1
         where id < (select min(id) from sub1 where rn is not null)
         order by id) x
 group by rn
 order by rn

I tested it in Oracle but it just relies on the with clause and window functions which I believe DB2 has so it should work, if anything with minor modifications.

Upvotes: 0

Clockwork-Muse
Clockwork-Muse

Reputation: 13096

Unless I miss my guess, this can be trivially done with a double-ROW_NUMBER() constant expression:

SELECT type, SUM(amount) AS amount
FROM (SELECT type, amount, id,
             ROW_NUMBER() OVER(ORDER BY id) 
                          - ROW_NUMBER() OVER(PARTITION BY type ORDER BY id) AS groupId
      FROM tbl) grouped
GROUP BY groupId, type
ORDER BY groupId, MIN(id)

SQL Fiddle Example
(Thanks to Brian for the initial fiddle setup)

...which yields the desired results. Note that MIN(id) is required to ensure a consistent sort order - groupId is not universally unique, so otherwise some ONE or TWO rows can end up flipped.

As a side note, I dislike ordering on an id column, as the only thing such an id is really good for is uniqueness. This is especially true in cases where rows may be updated or otherwise reinterpreted - do you have something like an insertedAt timestamp you could use for ordering instead?

Upvotes: 3

Related Questions