-
group By Aggragate
I need to group by an aggragate function. I have a bunch of users and I need to add up all their points, whether or not they have any. Need to list all users (and only distinct), then I need to sort by Points, Last Name.
Here is what I have. It has two problems. Unable to get all users and can not sort (group by) points.
QUERY:
SELECT SUM(Leave_Points.Points) AS MaxPoints, LeaveRequests.TakenBy, SUM(LeaveRequests.Hours) AS tltHours, LeaveRequests.LeaveDate,Users.DeptID
FROM Leave_Points INNER JOIN
LeaveRequests ON Leave_Points.LeaveID = LeaveRequests.LeaveID RIGHT OUTER JOIN
Users ON Leave_Points.UserID = Users.UserID
WHERE (Users.DeptID = '1') AND (LeaveRequests.LeaveDate>'1/1/2005')
Group BY Hours,TakenBy,LeaveDate,Users.DeptID
Result:
MaxPoints UserID Hours LeaveDate DeptID
0.0 8 4 2009-02-26 00:00:00 1
0.5 624 12 2009-02-23 00:00:00 1
0.5 624 12 2009-03-11 00:00:00 1
0.5 624 12 2009-03-17 00:00:00 1
0.5 648 12 2009-03-18 00:00:00 1
0.5 644 13 2009-02-27 00:00:00 1
1.0 7 24 2009-04-01 00:00:00 1
1.0 113 24 2009-04-15 00:00:00 1
1.0 643 24 2009-04-21 00:00:00 1
1.0 645 24 2009-03-05 00:00:00 1
1.0 656 24 2009-02-26 00:00:00 1
1.0 656 24 2009-04-23 00:00:00 1
1.0 657 24 2009-02-26 00:00:00 1
Any thoughts?
-
You can ORDER BY agg columns as in GROUP BY
SELECT SUM(Leave_Points.Points) AS MaxPoints, LeaveRequests.TakenBy, SUM(LeaveRequests.Hours) AS tltHours, LeaveRequests.LeaveDate,Users.DeptID
FROM Leave_Points INNER JOIN
LeaveRequests ON Leave_Points.LeaveID = LeaveRequests.LeaveID RIGHT OUTER JOIN
Users ON Leave_Points.UserID = Users.UserID
WHERE (Users.DeptID = '1') AND (LeaveRequests.LeaveDate>'1/1/2005')
Group BY Hours,TakenBy,LeaveDate,Users.DeptID
ORDER BY tltHours,TakenBy,LeaveDate,Users.DeptID
-
OK, great. That did sort in the manner that we expect. But I cannnot get all users from Users table to show up where the deptID=1.
Here is my new recordset:
MaxPoints TakenBy tltHours LeaveDate DeptID
0.0 8 4 2/26/2009 12:00:00 AM 1
0.5 624 12 2/23/2009 12:00:00 AM 1
0.5 624 12 3/11/2009 12:00:00 AM 1
0.5 624 12 3/17/2009 12:00:00 AM 1
0.5 648 12 3/18/2009 12:00:00 AM 1
0.5 644 13 2/27/2009 12:00:00 AM 1
1.0 7 24 4/1/2009 12:00:00 AM 1
1.0 113 24 4/15/2009 12:00:00 AM 1
1.0 643 24 4/21/2009 12:00:00 AM 1
1.0 645 24 3/5/2009 12:00:00 AM 1
1.0 656 24 2/26/2009 12:00:00 AM 1
1.0 656 24 4/23/2009 12:00:00 AM 1
1.0 657 24 2/26/2009 12:00:00 AM 1
-
GROUP BY may produce a distinct record, so you may not see duplicate rows.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|