Group by repeating values Table:

  



select 
    MainStationID,
    CONCAT(SUBSTRING(SubStationID,1,2),' STN - ',MainStationID) as Station,
    count(SubStationID) as [COUNT],
    CAST(EndDateTime-StartDateTime AS TIME(0)) AS Duration 
from 
    VHASM2.LineStoppageTrans 
where 
    SubStationID like 'RH%'
group by 
    MainStationID,
    SubStationID,
    CAST(EndDateTime-StartDateTime AS TIME(0)) 
order by 
    MainStationID 




Output:-



How to get output like this, please help

Mainstation   Station     count   Duration [total duration]
   1          RH STN - 1    2     00:01:09
   2          RH STN - 2    3     00:00:13
DECLARE @t TABLE (MainStationID int, SubStationID varchar(3), StartDateTime datetime, EndDateTime datetime);
INSERT INTO @t VALUES
(1, 'RH1', '20181219 17:24:08.440', '20181219 17:25:04.523'),
(1, 'RH1', '20181219 17:25:52.283', '20181219 17:26:05.280'),
(2, 'RH2', '20181219 18:36:51.530', '20181219 18:36:57.107'),
(2, 'RH2', '20181219 18:37:03.080', '20181219 18:37:05.090');

;WITH ct AS
(
    SELECT
        MainStationID,
        CONCAT(SUBSTRING(SubStationID, 1, 2), ' STN - ', MainStationID) as Station,
        COUNT(*) as [Count],
        SUM(DATEDIFF(SECOND, StartDateTime, EndDateTime)) as Duration
    FROM
        @t
    WHERE
        SubStationID LIKE 'RH%'
    GROUP BY
        MainStationID,
        SubStationID
)
SELECT
    MainStationID,
    Station,
    [Count],
    CONCAT(RIGHT('0' + CAST(Duration / 3600 AS varchar(3)), 2), ':',
           RIGHT('0' + CAST((Duration / 60) % 60 AS varchar(3)), 2), ':',
           RIGHT('0' + CAST(Duration % 60 AS varchar(3)), 2)) AS Duration
FROM
    ct
ORDER BY
    MainStationID,
    Station;




| MainStationID | Station    | Count | Duration |
|---------------|------------|-------|----------|
| 1             | RH STN - 1 | 2     | 00:01:09 |
| 2             | RH STN - 2 | 2     | 00:00:08 |