Sql Server calculating total minutes and the hours with group by clause

Posted on 2009-04-28
Last Modified: 2012-05-06
I have a select query and want to calculate the total minutes and the hours. If total minutes greater than 59 I want to add 1 hour into the hours column and using the mod function (%) calculate the rest of minutes. How can it be done in  a best way? Thanks.

select LastName,SUM(hours) as TotalHours, SUM(minutes) as TotalMinutes
from Table1 AS PA
inner join table2 as OT
Group by LastName
condition idea:

select CASE When sum(minutes)>59 Then sum(minutes)%60 Else sum(minutes)
End as TotalMinutes From Table1
Question by:fycert55
    1 Comment
    LVL 29

    Accepted Solution

    Hi fycert55,
    try this:
    select LastName,SUM(hours) + (CASE WHEN SUM(minutes) > 59 THEN SUM(minutes) 
          / 60 ELSE 0 END) AS TotalHours, CASE WHEN SUM(minutes) 
          > 59 THEN SUM(minutes) % 60 ELSE SUM(minutes) END AS TotalMinutes
    from Table1 AS PA
    inner join table2 as OT
    Group by LastName

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now