GROUP BY Specific Column and sum one columns consecutive values in SQL

Question

I have a dataset by using this query:

SELECT ThanaId, RoadId, (ToChain-FromChain) as TotalChain, FromChain, ToChain, SurfaceType FROM Segment WHERE ThanaId = (SELECT MIN(ThanaId) AS ThanaId FROM RdLineDiagTemp) AND RoadId = (SELECT MIN(RoadId) AS RoadId FROM RdLineDiagTemp) ORDER BY FromChain

The Dataset is:

 ThanaId | RoadId | TotalChain | FromChain | ToChain | SurfaceType 54701 |1368 |3100 |0 |3100 |BC 54701 |1368 |2000 |3100 |5100 |BC 54701 |1368 |750 |5100 |5850 |BC 54701 |1368 |920 |5850 |6770 |BC 54701 |1368 |73 |6770 |6843 |BC 54701 |1368 |2000 |6843 |8843 |BC 54701 |1368 |4007 |8843 |12850 |Ert 54701 |1368 |2000 |12850 |14850 |BC

I want to show it like this:

 ThanaId | RoadId | TotalChain | FromChain | ToChain | SurfaceType 54701 |1368 |8843 |0 |8843 |BC 54701 |1368 |4007 |8843 |12850 |Ert 54701 |1368 |2000 |12850 |14850 |BC

Here, each consecutive TotalChain is summed for the consecutive same surface type. What query should I write to achieve this?

How do you know consecutive surface type, there is no column to know the order. — Jatin Patel
– Jatin Patel, Commented Apr 12, 2018 at 7:06

Giorgos Betsos · Accepted Answer · 2018-04-12 07:23:53Z

This is a gaps and islands problem.

You can use a query like the following:

SELECT ThanaId, RoadId, SUM(TotalChain) AS TotalChain, MIN(FromChain) AS FromChain, MAX(ToChain) AS ToChain, SurfaceType FROM ( SELECT ThanaId, RoadId, (ToChain-FromChain) as TotalChain, FromChain, ToChain, SurfaceType, ROW_NUMBER() OVER (PARTITION BY ThanaId, RoadId ORDER BY FromChain) - ROW_NUMBER() OVER (PARTITION BY ThanaId, RoadId, SurfaceType ORDER BY FromChain) AS grp FROM Segment WHERE ThanaId = (SELECT MIN(ThanaId) AS ThanaId FROM RdLineDiagTemp) AND RoadId = (SELECT MIN(RoadId) AS RoadId FROM RdLineDiagTemp) ) AS t GROUP BY ThanaId, RoadId, SurfaceType, grp ORDER BY FromChain

Demo here

Note: I assume that field FromChain determines row order.

Yogesh Sharma · Accepted Answer · 2018-04-12 07:26:25Z

Use difference of row_numbers as Giorgos Betsos said this is a gaps and islands problem.

select ThanaId, RoadId, max(ToChain) - min(FromChain) TotalChain, min(FromChain) FromChain, max(ToChain) ToChain, SurfaceType from ( select *, row_number() over (order by ThanaId, RoadId) Seq1, row_number() over (partition by SurfaceType order by ThanaId, RoadId) Seq2 from table t ) tt group by ThanaId, RoadId, SurfaceType, (Seq1-Seq2)

Collectives™ on Stack Overflow

GROUP BY Specific Column and sum one columns consecutive values in SQL

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related