ISO SQL Select Help SUM(), Group By, OrderBy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ISO SQL Select Help SUM(), Group By, OrderBy

Author

Topic

GrepZen
Starting Member

13 Posts

Posted - 2007-05-02 : 10:34:26

'Lo All,
I'm looking to aggregate 'hours applied' for each user.
& I'm looking for a recordset that's something like:
GroupName, FName, LName, SUMHoursAppliedByUser

table:Users
UserID
LoginID
LName
FName

table:Groups
+GroupID
GroupName

table:lu_UsersGroups
GroupID
UserID

table:Categories
+CategoryID
CategoryName

table:History
+EventID
UserID
CategoryID
HoursApplied
DateApplied

My Current Select Statement:


SELECT Groups.GroupID, 
       Groups.GroupName, 
       Users.UserID, 
       Users.FName, 
       Users.LName, 
       History.HoursApplied, 
       History.DateApplied,
       SUM(History.hoursApplied) as SUMHoursByUser 
FROM History 
JOIN LU_UsersGroups on history.UserID = LU_UsersGroups.UserID 
JOIN Users on LU_UsersGroups.UserID = Users.UserID 
JOIN Groups on LU_UsersGroups.GroupID = Groups.GroupID 
WHERE Groups.GroupID = 1 OR groups.groupID = 3 
  AND (DateApplied >'4/25/2007 00:00:01 AM' 
  AND DateApplied <'5/2/2007 11:59:59 PM') 
GROUP BY Users.UserID 
ORDER BY LU_UsersGroups.GroupID, LU_UsersGroups.UserID

& of course, I get the error:
Column 'Groups.GroupID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

If anyone can provide:
[] some insight on proper way to aggregate
[] Some good reading on this
...I'd be very grateful.

Respectfully,

Micke

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-02 : 11:59:34

this might be helpful for you: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources