help with proc ( join + sum) - SQL Server Forums

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

Transact-SQL (2000)

help with proc ( join + sum)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-03-16 : 19:50:43

I have created the SPROC below and it works fine however I want to add one piece of functionality to it and I'm not exactly sure how to go about it. I want to add a column (totalPoints) as a SUM of a bunch rows.

I want totalpoints to be the SUM of each row named points WHERE the user is a part of the specific group.

I can get all the userID's for the specific group by running this query

Select userID from tblGroupUsers where groupID = @groupID
I can join onto tblUserDetails on "USERID" and get the column "points", this is the column I need to have a SUM for.

Any help is greatly appreciated

Thanks alot
Mike123

CREATE PROCEDURE dbo.select_GroupDetails (
@groupID int
)
AS SET NOCOUNT ON

SELECT GD.groupID, GD.groupName, GD.createdBy, UD.nameOnline FROM tblGroupDetails GD JOIN tblUserDetails UD on GD.createdBy = UD.userID WHERE groupID = @groupID

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 19:52:46

Got DDL for your tables and DML for sample data? It's hard to picture what someone wants without this information. Also, what would the expected result set be using the sample data?

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-03-16 : 20:33:28

Tara, sure here's some sample data. Hopefully this makes things alot easier, if theres something else I can include to make it easier please let me know.

Thanks again!

Mike123

TblGroupUsers

GROUPID / USERID / ACTIVE / JOINDATE
1 411 1 2004-03-16 14:43:00
1 156843 1 2004-01-01 00:00:00

------------------------------------------------

tblGroupDetails

GROUPID / GROUPNAME / CREATEDBY
1 MyGroupName 411

-----------------------------------------------

tblUserDetails

USERID / POINTS / NAMEONLINE
411 / 32423 / Mike
156843 / 5000 / Bob

A desired result would be something like this

GROUPID / GROUPNAME / CREATEDBY / TOTALPOINTS

1 / myGroupName / BOB / 37423

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-17 : 00:28:16

Does this work for you?


SELECT GD.groupID, GD.groupName, GD.createdBy, UD.nameOnline, SUM(UD1.TotalPoints) 
  FROM tblGroupDetails GD INNER JOIN tblUserDetails UD 
  ON GD.createdBy = UD.userID
  INNER JOIN tblGroupUsers GU
  ON GD.GroupID = GU.GroupID
  INNER JOIN tblUserDetails UD1
  ON GU.UserID = UD1.UserID
  WHERE GD.groupID = @groupID 

GROUP BY GD.groupID, GD.groupName, GD.createdBy, UD.nameOnline

EDIT: You might want to consider throwing in an OUTER JOIN from tblGroupDetails in case the group has no users at all. With an INNER JOIN you will get no results in that case.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources