SQL server aggregate function query

Question

I have a table called MusicTrack with the columns MusicTrackID, TrackName and Duration.

I am using the query:

    Select Cast(DateAdd( ms,SUM(DateDiff( ms, '00:00:00', Duration)), '00:00:00' ) as time) 
as 'Total duration' from   
    MusicTrack  where MusicTrackID = '1' or MusicTrackID = '3'

This query adds the durations of the 2 selected music tracks together and displays it in a temporary column called total duration. The 'Duration' is of datatype Time, so I am converting it to integer and back again.

My question: what way can I adapt the query to also include include the TrackName field and a running total duration? Or include the temporary column as well as the TrackName column.

So that the display will have TrackName and Total duration... along the lines of:

TrackName   Duration   Total duration

Name1       00:03:00     00:03:00
Name2       00:03:01     00:06:01

I tried to just include the TrackName column to the query like this, but it doesn't work:

Select TrackName, Cast(....) From MusicTrack  where MusicTrackID = '1' or MusicTrackID = '3'

Answer 1

try below

i created one sample table

SELECT * INTO TMPTIME 
 FROM (
SELECT 1 AS ID ,'Name1' AS NAME,'00:03:00' AS T    
UNION 
SELECT 2 AS ID,'Name2' AS NAME,'00:03:01' AS T    
UNION
SELECT  3 AS ID,'Name3' AS NAME,'00:03:02' AS T   
UNION
SELECT 4 AS ID,'Name4' AS NAME,'00:03:41' AS T    
)TMP

output query

SELECT ID,NAME, T AS TIME,
(SELECT 
cast(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', cast (t as time) )), '00:00:00.000') as time)
FROM TMPTIME T1 WHERE T1.ID<=TMPTIME.ID
) AS TOTAL

 FROM TMPTIME

result

ID  NAME    TIME    TOTAL
1   Name1   00:03:00    00:03:00.0000000
2   Name2   00:03:01    00:06:01.0000000
3   Name3   00:03:02    00:09:03.0000000
4   Name4   00:03:41    00:12:44.0000000