Link to home
Start Free TrialLog in
Avatar of CharlieDev
CharlieDevFlag for United Kingdom of Great Britain and Northern Ireland

asked on

sql syntax- Sum of multiple fields

Hi,
I an trying to add two fields up, one is minutes and one hours, I want to sum these two fields once i have converted hours into minutes, but I'm getting an error that i cant perform an aggregate function on an expression containing an aggregate or subquery.

 SUM((SUM(tblTimesheet.Hours)* 60)+ SUM(tblTimesheet.Minutes)) as SumHours

I need to get the hours and minutes like this as i need them to be totalled by employeefirstname and taskname.

Thanks
@ClientID UniqueIdentifier
AS
BEGIN
 
 
SELECT tblEmployee.[EmployeeFirstName] as EmployeeFirstName, tblTask.[TaskName] as TaskName,SUM((SUM(tblTimesheet.Hours)* 60)+ SUM(tblTimesheet.Minutes)) as SumHours
FROM tblTimesheet
INNER JOIN tblEmployee ON tblTimesheet.EmployeeID = tblEmployee.EmployeeID
INNER JOIN tblTask ON tblTimesheet.TaskID = tblTask.TaskID
WHERE ClientID = @ClientID
GROUP BY tblEmployee.[EmployeeFirstName], tblTask.[TaskName]
ORDER BY tblEmployee.[EmployeeFirstName] ASC
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ps:

SUmHours would actually be "wrong". it should be SumMinutes as by the formula :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CharlieDev

ASKER

Thanks to you all :)