?
Solved

sql syntax- Sum of multiple fields

Posted on 2009-02-10
5
Medium Priority
?
1,082 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:CharlieDev
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 23599252
remove the outer sum:
 (SUM(tblTimesheet.Hours)* 60)+ SUM(tblTimesheet.Minutes) as SumHours

Open in new window

0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 800 total points
ID: 23599253
Change the SELECT statement as follows -
SELECT tblEmployee.[EmployeeFirstName] as EmployeeFirstName, tblTask.[TaskName] as TaskName,SUM((tblTimesheet.Hours* 60) + 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

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23599260
ps:

SUmHours would actually be "wrong". it should be SumMinutes as by the formula :)
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 400 total points
ID: 23599267
SELECT tblEmployee.[EmployeeFirstName] as EmployeeFirstName, tblTask.[TaskName] as TaskName,(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
0
 

Author Closing Comment

by:CharlieDev
ID: 31544961
Thanks to you all :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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