CODEWITHSUNDEEP
sql-servert-sqlcumulative-sum
A.Davies
A.Davies

Reputation: 13

Running total/ID groups based on specific value in TSQL

I have data that looks like ID and Col1, where the value 01 in Col1 denotes the start of a related group of rows lasting until the next 01.

Sample Data:

ID  Col1    
1   01      
2   02      
3   02      
---------
4   01
5   02
6   03
7   03
----------
8   01
9   03
----------
10  01

I need to calculate GroupTotal, which provides a running total of '01' from Col1, and also GroupID, which is an increment ID that resets at every instance of '01' in Col 1. Row order must be preserved with ID.

Desired Results:

ID  Col1    GroupTotal  GroupID
1   01      1           1
2   02      1           2
3   02      1           3
----------------------------
4   01      2           1
5   02      2           2
6   03      2           3
7   03      2           4
----------------------------
8   01      3           1
9   03      3           2
----------------------------
10  01      4           1

I've been messing with OVER, PARTITION BY etc. and cannot crack either.

Thanks

Upvotes: 1

Views: 81

Answers (3)

tarheel
tarheel

Reputation: 4797

I believe what the OP is saying is that the only data available is a table with the id and col1 data, and that the desired results is what is currently posted in the question.

If that is the case, you just need the following.

Sample Data Setup:

declare @grp_tbl table (id int, col1 int)

insert into @grp_tbl (id, col1)
values (1, 1),(2, 2),(3, 2),(4, 1),(5, 2),(6, 3),(7, 3),(8, 1),(9, 3),(10, 1)

Answer:

declare @max_id int = (select max(id) from @grp_tbl)

; with grp_cnt as
    (
        --getting the range of ids that are in each group
        --and ranking them
        select gt.id
        , lead(gt.id - 1, 1, @max_id) over (order by gt.id asc) as id_max --max id in the group
        , row_number() over (order by gt.id asc) as grp_ttl
        from @grp_tbl as gt
        where 1=1
        and gt.col1 = 1
    )
--ranking the range of ids inside each group
select gt.id
, gt.col1
, gc.grp_ttl as group_total
, row_number() over (partition by gc.grp_ttl order by gt.id asc) as group_id
from @grp_tbl as gt
left join grp_cnt as gc on gt.id between gc.id and gc.id_max

Final Results:

  id    col1    group_total  group_id
    1     1       1           1
    2     2       1           2
    3     2       1           3
    4     1       2           1
    5     2       2           2
    6     3       2           3
    7     3       2           4
    8     1       3           1
    9     3       3           2
    10    1       4           1

Upvotes: 1

Steve
Steve

Reputation: 710

I'm not really sure what you are after but you are on the right tracks with partitioning functions. The following calculates a running total of groupid by grouptotal. I'm sure that's not what you want but it shows you how you can achieve it.

select  *, SUM(GroupId) over (partition by grouptotal order by id)
from #tmp
order by grouptotal, id

Upvotes: 0

James Z
James Z

Reputation: 12317

If I understood correctly, this is what you want:

CREATE TABLE #tmp
    ([ID] int, [Col1] int, [GroupTotal] int, [GroupID] int)
;

INSERT INTO #tmp
    ([ID], [Col1], [GroupTotal], [GroupID])
VALUES
    (1, 01, 1, 1),
    (2, 02, 1, 2),
    (3, 02, 1, 3),
    (4, 01, 2, 1),
    (5, 02, 2, 2),
    (6, 03, 2, 3),
    (7, 03, 2, 4),
    (8, 01, 3, 1),
    (9, 03, 3, 2),
    (10, 01, 4, 1)
;

select *, row_number() over (partition by Grp order by ID) as GrpID From (
    select ID, Col1, [GroupTotal], 
    sum(case when Col1 = '01' then 1 else 0 end) over (Order by ID) as Grp, 
    [GroupID]
from #tmp

The sum handles the groups with case, 1 is added always when Col1=01, and that's then used in the row_number to partition the groups.

Example

Upvotes: 0

Related Questions