CODEWITHSUNDEEP
sql-serversum
Abdulquadir Shaikh
Abdulquadir Shaikh

Reputation: 101

Add time to rows in SQL Server

I am trying to add values of duplicate rows in SQL Server. This is how data looks

 SESSION ID  |TALK TIME|TSEC|TMIN |
 ------------+---------+----+-----+
 34000018959 |00:34.0  |34  |0.56 |
 34000018959 |20:29.0  |1229|20.48|

This the query I am using to add integer

SELECT 
    [SESSION ID], 
    SUM([TALK TIME]) AS [TALK TIME], 
    SUM([TSEC]) AS [TSEC], 
    SUM([TMIN]) AS [TMIN]
FROM   
    [TABLE1]
GROUP BY 
    [SESSION ID], [TALK TIME]

No using this I am able to sum TSEC and TMIN how I would like. But not able to get SUM of Talk Time.

This is what output I would like to see:

 SESSION ID  |TALK TIME|TSEC|TMIN |
 ------------+---------+----+-----+
 34000018959 |21:03.0  |1263|21.05|

Upvotes: 3

Views: 260

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

One simple approach is to sum the seconds and derive the rest

Example

Declare @T table ([SESSION ID] varchar(50),[TALK TIME] varchar(50),tSec int,tmin decimal(10,2))
Insert Into @T values 
('34000018959','00:34.0',34,0.56),
('34000018959','20:29.0',1229,20.48)

Select [Session ID]
      ,[Talk Time] = format(dateadd(SECOND,sum(tSec),0),'mm:ss.f')
      ,TSec = sum(tSec)
      ,TMin = cast(sum(tSec+0.0)/60 as decimal(10,2))
 From  @T
 Group By [Session ID]

Returns

Session ID  Talk Time   TSec    TMin
34000018959 21:03.0     1263    21.05

Upvotes: 3

Related Questions