Query SQL group by per next row if have same value?-CodePudding

The group by method is if the value of the next column is the same, I will calculate how long the unit has been in a certain place, later added per group

BEFORE

| ID         | UNIT  | PositionDateTime        | Location |   
| ---        | ----- | ----------------------- | -------- |
| 2054431321 | PM1041| 2022-09-25 00:05:55.000 | CP 33    |
| 2054776699 | PM1041| 2022-09-25 00:06:53.000 | KM 33    |
| 2054431444 | PM1041| 2022-09-25 00:06:53.000 | KM 33    |
| 2056063402 | PM1041| 2022-09-25 00:06:55.000 | KM 33    |
| 2054431444 | PM1041| 2022-09-25 00:11:55.000 | KM 31    |
| 2056064084 | PM1041| 2022-09-25 00:20:55.000 | KM 33    |
| 2056065697 | PM1041| 2022-09-25 00:30:55.000 | KM 33    |
| 2059291255 | PM1041| 2022-09-25 02:05:55.000 | CP 33    |
| 2059291566 | PM1041| 2022-09-25 02:15:55.000 | CP 33    |
| 2054776808 | PM1041| 2022-09-25 02:21:55.000 | KM 31    |
| 2060581645 | PM1041| 2022-09-25 02:22:55.000 | KM 31    |

GROUP TO

UNIT  | PositionDateTimeStart    | PositionDateTimeEnd     | Location |   
----- | -----------------------   --------------------     | --------
PM1041| 2022-09-25 00:05:55.000  | 2022-09-25 00:05:55.000 | CP 33    |
PM1041| 2022-09-25 00:06:53.000  | 2022-09-25 00:06:55.000 | KM 33    |
PM1041| 2022-09-25 00:11:55.000  | 2022-09-25 00:11:55.000 | KM 31    |
PM1041| 2022-09-25 00:20:55.000  | 2022-09-25 00:30:55.000 | KM 33    |
PM1041| 2022-09-25 02:05:55.000  | 2022-09-25 02:15:55.000 | CP 33    |
PM1041| 2022-09-25 02:21:55.000  | 2022-09-25 02:22:55.000 | KM 31    |

CodePudding user response：

This is a classic Gaps-and-Islands Problem.

In the future, please provide sample data as TEXT not as an image.

Example

with cte as (
    Select *
          ,Grp = row_number() over (order by ID) 
                -row_number() over (partition by BUAH order by ID)
    From  YourTable
)
Select BUAH
      ,Cnt = sum(1)
 From cte
 Group By BUAH,Grp
 Order By min(ID)   --<< Optional

Results

BUAH    Cnt
APEL    2
TOMAT   2
APEL    3
...

CodePudding user response：

User COUNT with GROUP BY to achieve it.

SELECT BUAH, COUNT(*) AS Total FROM your_table GROUP BY WAKTU BELI