Resolving hours and minutes from total minutes in T-SQL

I'm trying to get hours and minutes from a tot开发者_如何学运维al minute count. I got this bit of code from another website, but the first part doesn't seem to work:

cast(TimeToAdd/60 as varchar) + ' hours ' +
cast(TimeToAdd%60 as varchar) + ' minutes' as [Converted]

With TimeToAdd worth 75 minutes, the answer should be 1 hour 15 minutes, but all I am getting is 0 hour 15 minutes.

Here is the full SQL:

select pu.ProjectID, c.ClientName, p.ProjectTitle, sum(TimeToAdd) as TotalTime, 
cast(TimeToAdd/60 as varchar) + ' hours ' +
cast(TimeToAdd%60 as varchar) + ' minutes' as [Converted]
from dbo.ProjectUsers pu
left join dbo.Projects p
on pu.ProjectID = p.ProjectID
left join dbo.ProjectTime pt
on p.ProjectID = pt.ProjectID
inner join dbo.Clients c
on p.ClientID = c.ClientID
where pu.StaffID = 3
group by pu.ProjectID, c.ClientName, p.ProjectTitle, pt.TimeToAdd

Any suggestions?

Use a subselect, so you can reference the SUMmed column by name:

select
  *,
  cast(TotalTime/60 as varchar) + ' hours ' +
  cast(TotalTime%60 as varchar) + ' minutes' as [Converted]
from (
  select
    pu.ProjectID,
    c.ClientName,
    p.ProjectTitle,
    sum(TimeToAdd) as TotalTime
  from dbo.ProjectUsers pu
    left join dbo.Projects p on pu.ProjectID = p.ProjectID
    left join dbo.ProjectTime pt on p.ProjectID = pt.ProjectID
    inner join dbo.Clients c on p.ClientID = c.ClientID
  where pu.StaffID = 3
  group by
    pu.ProjectID,
    c.ClientName,
    p.ProjectTitle
) s

This works for me, as below:

DECLARE @TimeToAdd INT

SET @TimeToAdd = 75

DECLARE @Hours nvarchar(50)
DECLARE @Minutes nvarchar(50)

SELECT @Hours = CAST(@TimeToAdd/60 as nvarchar) + ' hours'
SELECT @Minutes = CAST(@TimeToAdd%60 as nvarchar) + ' minutes'

PRINT @Hours
PRINT @Minutes

Output:

1 hours
15 minutes

select pu.ProjectID, c.ClientName, p.ProjectTitle, sum(TimeToAdd) as TotalTime, 
cast(FLOOR(sum(TimeToAdd)/60.0) as varchar) + ' hours ' +
cast(sum(TimeToAdd)%60 as varchar) + ' minutes' as [Converted]
from dbo.ProjectUsers pu
left join dbo.Projects p
on pu.ProjectID = p.ProjectID
left join dbo.ProjectTime pt
on p.ProjectID = pt.ProjectID
inner join dbo.Clients c
on p.ClientID = c.ClientID
where pu.StaffID = 3
group by pu.ProjectID, c.ClientName, p.ProjectTitle // please remove (I guess), pt.TimeToAdd