CODEWITHSUNDEEP
sqlsql-server
DAR
DAR

Reputation: 49

SQL Server - aggregate the data

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

Upvotes: 1

Views: 59

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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);

Upvotes: 3

Related Questions