CODEWITHSUNDEEP
sqlt-sql
Nxtq
Nxtq

Reputation: 7

SQL function to group consecutive rows

I have a case that was originally done in excel that I'm trying to replicate in a query. I have data structured as follows:

Field1 | Field2
---------------
1      |5
2      |10
3      |5
4      |10
5      |5

I need a function that can group by/agg by consecutive 3 rows such that I return with:

Field1 | Field2
---------------
123    |20
234    |25
345    |20

Upvotes: 0

Views: 34

Answers (1)

The Impaler
The Impaler

Reputation: 48875

Use the LEAD() function, as in:

with
x as (
  select
    field1 as x1,
    lead(field1) as x2,
    lead(field1, 2) as x3,
    field2 as y1,
    lead(field2) as y2,
    lead(field2, 2) as y3
  from my_table
)
select
  x1 + x2 + x3 as field1,
  y1 + y2 + y3 as field2
from x
where x1 is not null
  and x2 is not null
  and x3 is not null

Upvotes: 1

Related Questions