Calculate sum of duration SQL

I use the following sql to calculate the duration in the first query.

CDate(TimeSerial(Val([EndTime])100,Val([EndTime]) Mod 100,0)-TimeSerial(Val([StartTime])100,Val([StartTime]) Mod 100,0))) AS Duration

And get the following which is good

And now, I would like to further calculate the total time of different lessons within a week in another query. Something like sum(iif(lesson=”math”,duration,0))

But since it’s time data type sql server doesn’t let me use the sum function.

I tried the following sql.

sum(hour(duration) + minute(duration)) AS Total_time

But it isn’t what I expect because I would like to keep the original format with some criteria.

Is there a neat way to do so? Thank you.

Answer

First, just get the numeric duration:

TimeSerial(Val([EndTime])100,Val([EndTime]) Mod 100,0)-TimeSerial(Val([StartTime])100,Val([StartTime]) Mod 100,0) AS Duration

This you can sum and convert to DateTime:

CDate(Sum([duration])) AS Total_time

Example:

Data:

Queries:

SELECT 
    StartTime, 
    EndTime, 
    TimeSerial(Val([EndTime])100,Val([EndTime]) Mod 100,0)-TimeSerial(Val([StartTime])100,Val([StartTime]) Mod 100,0) AS Duration
FROM 
    tTest;

SELECT 
    Count(*) AS Slots, 
    CDate(Sum([Duration])) AS TotalHours
FROM 
    qTest;

Result: