Results 1 to 4 of 4

Thread: group By Aggragate

  1. #1
    Join Date
    Apr 2009
    Posts
    1

    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?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  3. #3
    Join Date
    Apr 2009
    Posts
    1
    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

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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
  •