CODEWITHSUNDEEP
sqloracle12c
Anish
Anish

Reputation: 758

Oracle SQL: Splitting a group by based on multiple rows criteria

I have data where a single address can have multiple journeys, something like this:

enter image description here

I want to split the address by each journey ("Start of Journey" to "End of Journey" and all "Part of Journeys" in the middle constitute one Journey) and aggregate the costs, so each address can have multiple rows based on the number of journeys, something like this:

enter image description here

I just can't seem to figure out the logic that I would need to use to solve this. Any help or pointers would be highly appreciated.

Upvotes: 0

Views: 772

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use a running sum to tag rows into groups, starting a new number whenever Start of Journey row is encountered for a given address. Then use it to group and get the total cost.

select address,sum(cost) 
from (select t.*
      --Replace OrderCol with a column that specifies row order
      ,sum(case when journey='Start of Journey' then 1 else 0 end) over(partition by address order by OrderCol) as grp 
      from tbl
     ) t
group by address,grp

Upvotes: 2

Related Questions