SQL query grouping by range

Hi have a table A with the following data:

+------+-------+----+--------+
| YEAR | MONTH | PA | AMOUNT |
+------+-------+----+--------+
| 2020 | 1     | N  | 100    |
+------+-------+----+--------+
| 2020 | 2     | N  | 100    |
+------+-------+----+--------+
| 2020 | 3     | O  | 100    |
+------+-------+----+--------+
| 2020 | 4     | N  | 100    |
+------+-------+----+--------+
| 2020 | 5     | N  | 100    |
+------+-------+----+--------+
| 2020 | 6     | O  | 100    |
+------+-------+----+--------+

I’d like to have the following result:

+---------+---------+--------+
| FROM    | TO      | AMOUNT |
+---------+---------+--------+
| 2020-01 | 2020-02 | 200    |
+---------+---------+--------+
| 2020-03 | 2020-03 | 100    |
+---------+---------+--------+
| 2020-04 | 2020-05 | 200    |
+---------+---------+--------+
| 2020-06 | 2020-06 | 100    |
+---------+---------+--------+

My DB is DB2/400. I have tried with ROW_NUMBER partitioning, subqueries but I can’t figure out how to solve this.

Answer

I understand this as a gaps-and-island problem, where you want to group together adjacent rows that have the same PA.

Here is an approach using the difference between row numbers to build the groups:

select min(year_month) year_month_start, max(year_month) year_month_end, sum(amount) amount
from (
    select a.*, year * 100 + month year_month
        row_number() over(order by year, month) rn1,
        row_number() over(partition by pa order by year, month) rn2
    from a
) a
group by rn1 - rn2
order by year_month_start