CODEWITHSUNDEEP
mysqlsqlsql-server
user3103672
user3103672

Reputation: 51

SQL server aggregate function query

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'

Upvotes: 5

Views: 70

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

sqlFiddle for SQL Server (not mySQL)

SELECT TrackName, Duration, Cast(DateAdd(ms,RunningTotal,'00:00:00') as time) as 'Total Duration'
FROM
(SELECT TrackName, Duration,
  SUM(DateDiff(ms,'00:00:00',Duration)) OVER(ORDER BY MusicTrackId 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
      AS RunningTotal
  FROM MusicTrack
  WHERE MusicTrackId IN (1,3)
)T

syntax found here running total

for mySql sqlFiddle, (NOT SQL Server)

SELECT TrackName,Date_Format(Duration,'%k:%i:%s') as Duration,
                 Date_Format(runningTotal,'%k:%i:%s') as 'Total Duration'
FROM
  (SELECT TrackName, Duration,
          Sec_To_Time(@total:=@total + Time_To_Sec(Duration)) as runningTotal
   FROM MusicTrack,(SELECT @total:='00:00:00')T
   WHERE MusicTrackId IN (1,3)
   ORDER BY MusicTrackId
  )Result

Upvotes: 0

sumit
sumit

Reputation: 15464

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

Upvotes: 2

Related Questions