Group by occurrence of row

Question

have a table like below where I have to take sum of col2 based on group by. But it belongs to a chain, if chain breaks then sum will be limited to sequence.

DECLARE @TabVar TABLE
(
  ID INT IDENTITY(1,1), col1 varchar(20), Col2 INT
)
INSERT INTO @TabVar
VALUES ('a',2),('a',3),('b',4),('b',2),('a',6),('a',3),('b',3)

SELECT * FROM @TabVar

 Expected output:
  COL1     SUM(COL2)
  A          5
  B          6
  A          9
  B          3

I have tried to do it with Ranking functions but ranking is done using Order by which accumulate total of Col1

ughai · Accepted Answer · 2015-05-15T07:07:49.863

2

You can use a ROW_NUMBER() with PARTITION BY and then a GROUP BY to achieve this.

Whenever your chain breaks, id - ROW_NUMBER()over(partition by col1 order by id) will have a different value for the same col1 value. You can then use this along with col1 to group your data and do a SUM. Something like this

Sample Data

DECLARE @TabVar TABLE
(
  ID INT IDENTITY(1,1), col1 varchar(20), Col2 INT
)
INSERT INTO @TabVar
VALUES ('a',2),('a',3),('b',4),('b',2),('a',6),('a',3),('b',3)

Query

SELECT Col1,SUM(Col2) sumcol
FROM
(
SELECT id - ROW_NUMBER()over(partition by col1 order by id) grpcol,Col1,Col2,id
FROM @TabVar
)T
GROUP BY grpcol,Col1
ORDER BY MAX(ID)

Output

Col1 sumcol
a   5
b   6
a   9
b   3

Edit

Incase your IDs are not consecutive in live environment, you can use this

SELECT Col1,SUM(Col2) sumcol
FROM
(
SELECT ROW_NUMBER()over(order by id)  - ROW_NUMBER()over(partition by col1 order by id) grpcol, Col1,Col2,id
FROM @TabVar
)T
GROUP BY grpcol,Col1
ORDER BY MAX(ID)

edited May 15 '15 at 07:07

answered May 15 '15 at 06:53

ughai

9,830
3
29
47

1

`Identities may not be consecutive`: check this http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values. in a certain case it would fail. for example take this data and run your query : DECLARE @TabVar TABLE ( ID INT, col1 varchar(20), Col2 INT ) INSERT INTO @TabVar VALUES (1,'a',2),(3,'a',3),(4,'b',4),(5,'b',2),(6,'a',6),(7,'a',3),(8,'b',3) – Deep May 15 '15 at 07:01
1

@DeepakPawar - my solution is based on the current sample provided by OP. Here `id` will be consecutive since there is no `RESEED` or transaction rollback involved which are primary reasons of identities to not be consecutive. a more generic solution would be a `row_number() over(order by id)` – ughai May 15 '15 at 07:05

Group by occurrence of row

1 Answers1