Grouping By Aggragate Function 1

[ItHurtsWhenIThink]

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.
Markros was a great help on this, but I was never able to find a solution.

SELECT DISTINCT(LeaveRequests.TakenBy),SUM(Leave_Points.Points) AS MaxPoints, SUM(LeaveRequests.Hours) AS tltHours, LeaveRequests.LeaveDate,Users.DeptID
FROM Leave_Points INNER JOIN
LeaveRequests ON Leave_Points.LeaveID = LeaveRequests.LeaveID AND LeaveRequests.LeaveDate>'1/1/2005' RIGHT OUTER JOIN
Users ON Leave_Points.UserID = Users.UserID
WHERE (Users.DeptID = '1')
Group BY Hours,TakenBy,LeaveDate,Users.DeptID

RESULT:

UserID Points Hours LeaveDate DeptID
NULL NULL NULL NULL 1
7 1.0 24 2009-04-01 00:00:00 1
8 0.0 4 2009-02-26 00:00:00 1
113 1.0 24 2009-04-15 00:00:00 1
624 0.5 12 2009-02-23 00:00:00 1
624 0.5 12 2009-03-11 00:00:00 1
624 0.5 12 2009-03-17 00:00:00 1
643 1.0 24 2009-04-21 00:00:00 1
644 0.5 13 2009-02-27 00:00:00 1
645 1.0 24 2009-03-05 00:00:00 1
648 0.5 12 2009-03-18 00:00:00 1
656 1.0 24 2009-02-26 00:00:00 1
656 1.0 24 2009-04-23 00:00:00 1
657 1.0 24 2009-02-26 00:00:00 1

Any thought?

 

[PDreyer]

To group by an aggregate you should use a derived table

I am unable to decipher what you want using your broken query. Give some sample date for your expected output.

 

[ItHurtsWhenIThink]

I need to have all users from the Users table where deptID=1. So this means that some users will not have any points.


UserID Points Hours LeaveDate DeptID
536 null null null 1 //see note
89 null null null 1 //see note
244 null null null 1 //see note
129 0.0 8 2009-02-26 00:00:00 1
8 0.0 4 2009-02-26 00:00:00 1
624 0.5 12 2009-02-23 00:00:00 1
624 0.5 12 2009-03-11 00:00:00 1
624 0.5 12 2009-03-17 00:00:00 1
644 0.5 13 2009-02-27 00:00:00 1
648 0.5 12 2009-03-18 00:00:00 1
7 1.0 24 2009-04-01 00:00:00 1
113 1.0 24 2009-04-15 00:00:00 1
643 1.0 24 2009-04-21 00:00:00 1
645 1.0 24 2009-03-05 00:00:00 1
656 1.0 24 2009-02-26 00:00:00 1
656 2.0 48 2009-04-23 00:00:00 1
657 2.0 48 2009-02-26 00:00:00 1

//I think the query will spit out a null value where no users are in leaverequest or points table.

 

[rajeevnandanmishra]

Kindly remove "Hours" from group by.
And use "Order By 2" for sorting on Points column.

 

[markros]

It seems to me that you need to start with the users

and join with the derived table from
select .. from Users LEFT JOIN
(select ... from Leave_Points INNER JOIN Leave_Requestes)

I'm not writing the query for you, I'm just thinking.

 

[RyanEK]

Hi,

How about something along the lines of...

select distinct(u.UserID), 
isnull(sum(lp.points),0) as points, 
isnull(sum(lr.hours),0) as hours
from [users] u
left outer join [leave_points] lp on u.UserID = lp.UserID
left outer join [leaverequests] lr on lp.leaveid = lr.leaveid
where u.deptid = 1
group by u.UserID

Ryan

 

[ItHurtsWhenIThink]

Ryan, That worked well... thanks

I wanted to begin from simple and work to complex. So I added to your statement.

select distinct(u.UserID),u.LName,U.FName,u.UserType,
isnull(sum(lp.points),0) as points,
isnull(sum(lr.hours),0) as hours
from [users] u
left outer join [leave_points] lp on u.UserID = lp.UserID
left outer join [leaverequests] lr on lp.leaveid = lr.leaveid
where u.deptid = 1
group by u.UserType,u.LName,u.FName,u.UserID
Order By u.UserType,Points,u.LName

Result:

UserID LName FName UserType points hours
130 Anderson Josh Captain 0.0 0
42 Branco Brian Captain 0.0 0
13 Ed Dang Captain 0.0 0
511 Marganski Ben Captain 0.0 0
110 Winters Donald Captain 0.0 0
657 Maginis Ted Captain 1.0 24
643 Sproles Kirk Captain 1.0 24
6 Banana Anna Engineer 0.0 0
535 dgdhjndh dhndhn Engineer 0.0 0
642 Dumas Jerry Engineer 0.0 0
658 Kellaway Michael Engineer 0.0 0
137 lapre eric Engineer 0.0 0
127 Moers Chris Engineer 0.0 0
12 Skalansky Marvin Engineer 0.0 0
40 Spencer Mike Engineer 0.0 0
7 Martian Marvin Engineer 1.0 24
624 Travis Web Engineer 1.5 36
656 Miller Bruce Engineer 2.0 48
655 Burger Ralph Firefighter 0.0 0
14 Lujan Oscar Firefighter 0.0 0
120 Nelson Fred Firefighter 0.0 0
128 Olsen Tod Firefighter 0.0 0
129 Pinkerton Phil Firefighter 0.0 0
629 Swann Matt Firefighter 0.0 0
450 test test Firefighter 0.0 0
648 Franklin Phil Firefighter 0.5 12
644 Hector Gonzal Firefighter 0.5 13
645 Edmond Mike Firefighter 1.0 24
113 gonzales Dan Firefighter 1.0 24
8 Fudd Elmer Mechanic 0.0 4
959 Joe Bob Mechanic 0.0 0
956 Moss James Mechanic 0.0 0
626 James McKinsy NA 0.0 0
17 Ortiz Paul NA 0.0 0
630 Stanley Hanson NA 0.0 0
1 Adams Tony Operations Chief 0.0 0
148 Johnson Brad Operations Chief 0.0 0

I'll play with it some more, but this was great help.

Now I'll need to create another statement that allows me to do the same, display all users but only values (points/hours) for current year quarter. I'll work on it for a while before asking for help... need to learn this stuff. Again, thanks.