sql - SQL Server - aggregate the data

Question

The below is the data I have. How to aggregate the data based on ID and Room but if the room is repeating it should not aggregate to the first appearance?

It should be aggregated like the below result set.

Data set:

ID      Room    Length  AD
-----------------------------------
1001    MM        2     2018-06-26
1001    MM        3     2018-06-26
1001    MM        0.5   2018-06-26
1001    MM        3     2018-06-28
1001    A5        3.5   2018-06-29
1001    MM        4.4   2018-06-29
1001    MM        3.2   2018-06-30
1001    A5        2.1   2018-07-02
1001    A5        1.7   2018-07-03

Expected result:

ID     Room   Sum_Length
-----------------------
1001    MM      8.5
1001    A5      3.5
1001    MM      7.6
1001    A5      3.8

标签： sqlsql-server

Answer 1

It seems you need differences of row_number() :

select id, room, sum(Length)
from (select t.*, row_number() over (partition by id order by ad) seq1,
                  row_number() over (partition by id, room order by ad) seq2
      from table t
     ) t
group by id, room, (seq1 - seq2);