Get sum qty over specific range-CodePudding

I have the below table

substring(area,6,3)	qty
101	10
103	15
102	11
104	30
105	25
107	17
108	23
106	48

And I am looking to get a result as below without repeating the IIF ( as it's a cumulative of 4 sequences) in the area:

new_area(substring(area,6,3)	sum_qty
101-104	66
105-108	117

I don't know how to create the new area column to be able to get the sum qty

Looking forward to your help.

Please also add an explanation so I will understand how the query is running.

CodePudding user response：

I think this is what you are looking for.

We just use the window function row_number() to create the Grp

NOTE: If you have repeating values in AREA use dense_rank() instead of row_number()

Example

Select new_area = concat(min(area),'-',max(area))
      ,qty = sum(qty)
 From (
        Select area=substring(area,6,3)
              ,qty
              ,Grp = (row_number() over (order by substring(area,6,3))-1) / 4 
         From  YourTable
      ) A
 Group By Grp

Results

new_area    qty
101-104     66
105-108     113  -- get different results

If you were to run the subquery, you would see the following.

Then it becomes a small matter to aggregate the data grouped by the created column GRP