BRAINBUSTER select statement - 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 2005 Forums

Transact-SQL (2005)

BRAINBUSTER select statement

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-01 : 13:50:08

ok this select statement works perfect (with the help of many of you)

what it does is give me ..grouped by ID .. the total for the "sum of points" for the Highest 8 out of 12 entries.

what i want to do now is include (grouped the same way) the sum of two other fields in the same table MTGPoints and DeBriefPoints...but here is the catch...i need the sum of both those fields to be for all 12 entries not the highest 8

as always any help would be appreciated

SELECT a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName, SUM(a.Points) AS [YTD Points]
FROM (SELECT dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName, dbo.WTournament_Points.Points AS Points, Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix,dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName
ORDER BY dbo.WTournament_Points.Points DESC) AS RowID
FROM dbo.WTournaments INNER JOIN
dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID INNER JOIN
dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID INNER JOIN
dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place

WHERE dbo.WTournaments.TournYear = @TournYear AND dbo.WTournaments.TournYearInc = 1) a

WHERE a.RowID <= 8 and Points > 0

GROUP BY a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName

ORDER BY [YTD Points] DESC

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-01 : 14:00:35

add an inner join to a subquery which totals them.
assuming the WTournaments is the correct source table...change as needed.

add TotalMTG, TotalDeBrief to your Select Statement and this to the FROM (before the Where clause)

INNER JOIN
(Select TRUSerID,SUm(MTGpoints) as TotalMTG, Sum(DeBriefPoints) as TotalDeBrief
FROM WTournaments
GROUP by TRUserID) Total
on WTournaments.TRUserID = Total.TRUSerID

Poor planning on your part does not constitute an emergency on my part.

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-01 : 14:13:59

sorry..i am a newbie and am having problems
i did this but got errors...maybe i am typing it in wrong?

SELECT a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName, SUM(a.Points) AS [YTD Points]
FROM (SELECT dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName,
TRDebriefPoints, TRMtgPoints, dbo.WTournament_Points.Points AS Points, Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix,
dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName
ORDER BY dbo.WTournament_Points.Points DESC) AS RowID
FROM dbo.WTournaments INNER JOIN
dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID INNER JOIN
dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID INNER JOIN
dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
INNER JOIN
(Select TRUSerID,SUm(TRDebriefPoints) as TotalDEBRIEF, Sum(TRMtgPoints) as TotalMTG
FROM WTournaments
GROUP by TRUserID) Total
on WTournaments.TRUserID = Total.TRUSerID
WHERE dbo.WTournaments.TournYear = @TournYear AND dbo.WTournaments.TournYearInc = 1) a
WHERE a.RowID <= 8 and Points > 0
GROUP BY a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName
ORDER BY [YTD Points] DESC

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-01 : 14:21:19

You added them in the wrong place but it helps if you actually describe the error you received. You also selected the source columns instead of the resulting sum...try the below and see if it works.


SELECT a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName, SUM(a.Points) AS [YTD Points],TotalDEBRIEF, TotalMTG
FROM (SELECT dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName,
 dbo.WTournament_Points.Points AS Points, Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix,
dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName
ORDER BY dbo.WTournament_Points.Points DESC) AS RowID
FROM dbo.WTournaments INNER JOIN
dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID INNER JOIN
dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID INNER JOIN
dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
INNER JOIN
(Select TRUSerID,SUm(TRDebriefPoints) as TotalDEBRIEF, Sum(TRMtgPoints) as TotalMTG
FROM WTournaments
GROUP by TRUserID) Total
on WTournaments.TRUserID = Total.TRUSerID
WHERE dbo.WTournaments.TournYear = @TournYear AND dbo.WTournaments.TournYearInc = 1) a
WHERE a.RowID <= 8 and Points > 0
GROUP BY a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName
ORDER BY [YTD Points] DESC

You tried adding them to the rowID subquery. My understanding was you just wanted the sum of those to items added to the results.

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-01 : 15:10:56

[code];WITH cte
AS (
SELECT dbo.WTournament_Results.TRUserID,
dbo.WUsers.Suffix,
dbo.WUsers.UserFirstName,
dbo.WUsers.UserLastName,
dbo.WTournament_Points.Points AS Points,
Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID ORDER BY dbo.WTournament_Points.Points DESC) AS RowID,
{Table name here}.MTGPoints,
{Table name here}.DeBriefPoints
FROM dbo.WTournaments
INNER JOIN dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID
INNER JOIN dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID
INNER JOIN dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
WHERE dbo.WTournaments.TournYear = @TournYear
AND dbo.WTournaments.TournYearInc = 1
)

SELECT TRUserID,
Suffix,
UserFirstName,
UserLastName,
SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) AS [YTD Points],
SUM(MTGPoints) AS MTGPoints,
SUM(DeBriefPoints) AS DeBriefPoints
FROM cte
WHERE Points > 0
GROUP BY TRUserID,
Suffix,
UserFirstName,
UserLastName
ORDER BY SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-01 : 18:52:16

almost...below is the whole store procedure

the error i get is

Msg 207, Level 16, State 1, Procedure WPRO_Get_Tournament_YTD, Line 20
Invalid column name 'TotalDEBRIEF'.
Msg 207, Level 16, State 1, Procedure WPRO_Get_Tournament_YTD, Line 20
Invalid column name 'TotalMTG'.

ALTER PROCEDURE [dbo].[WPRO_Get_Tournament_YTD]
-- Add the parameters for the stored procedure here
(
@TournYear int
)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName, SUM(a.Points) AS [YTD Points],TotalDEBRIEF, TotalMTG
FROM (SELECT dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName,
dbo.WTournament_Points.Points AS Points, Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix,
dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName
ORDER BY dbo.WTournament_Points.Points DESC) AS RowID
FROM dbo.WTournaments INNER JOIN
dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID INNER JOIN
dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID INNER JOIN
dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place INNER JOIN
(Select TRUSerID,SUm(TRDebriefPoints) as TotalDEBRIEF, Sum(TRMtgPoints) as TotalMTG
FROM WTournament_Results
GROUP by TRUserID)TOTAL
on WTournament_Results.TRUserID = Total.TRUSerID
WHERE dbo.WTournaments.TournYear = @TournYear AND dbo.WTournaments.TournYearInc = 1) a
WHERE a.RowID <= 8
GROUP BY a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName
ORDER BY [YTD Points] DESC
END

quote:

Originally posted by Peso

;WITH cte
AS (
	SELECT		dbo.WTournament_Results.TRUserID,
			dbo.WUsers.Suffix,
			dbo.WUsers.UserFirstName,
			dbo.WUsers.UserLastName,
			dbo.WTournament_Points.Points AS Points,
			Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID ORDER BY dbo.WTournament_Points.Points DESC) AS RowID,
			{Table name here}.MTGPoints,
			{Table name here}.DeBriefPoints
	FROM		dbo.WTournaments
	INNER JOIN	dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID
	INNER JOIN	dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID
	INNER JOIN	dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
	WHERE		dbo.WTournaments.TournYear = @TournYear
			AND dbo.WTournaments.TournYearInc = 1
)

SELECT		TRUserID,
		Suffix,
		UserFirstName,
		UserLastName,
		SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) AS [YTD Points],
		SUM(MTGPoints) AS MTGPoints,
		SUM(DeBriefPoints) AS DeBriefPoints
FROM		cte
WHERE		Points > 0
GROUP BY	TRUserID,
		Suffix,
		UserFirstName,
		UserLastName
ORDER BY	SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) DESC

E 12°55'05.25"
N 56°04'39.16"

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-01 : 19:21:40

PESO I DONT UNDERSTAND YOURS BELOW???

quote:

Originally posted by Peso

;WITH cte
AS (
	SELECT		dbo.WTournament_Results.TRUserID,
			dbo.WUsers.Suffix,
			dbo.WUsers.UserFirstName,
			dbo.WUsers.UserLastName,
			dbo.WTournament_Points.Points AS Points,
			Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID ORDER BY dbo.WTournament_Points.Points DESC) AS RowID,
			{Table name here}.MTGPoints,
			{Table name here}.DeBriefPoints
	FROM		dbo.WTournaments
	INNER JOIN	dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID
	INNER JOIN	dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID
	INNER JOIN	dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
	WHERE		dbo.WTournaments.TournYear = @TournYear
			AND dbo.WTournaments.TournYearInc = 1
)

SELECT		TRUserID,
		Suffix,
		UserFirstName,
		UserLastName,
		SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) AS [YTD Points],
		SUM(MTGPoints) AS MTGPoints,
		SUM(DeBriefPoints) AS DeBriefPoints
FROM		cte
WHERE		Points > 0
GROUP BY	TRUserID,
		Suffix,
		UserFirstName,
		UserLastName
ORDER BY	SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) DESC

E 12°55'05.25"
N 56°04'39.16"

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-01 : 20:56:05

I GOT IT...YOU GUYS ROCK...THANKS PESO I USED YOURS

quote:

Originally posted by joemama

PESO I DONT UNDERSTAND YOURS BELOW???

quote:

Originally posted by Peso

;WITH cte
AS (
	SELECT		dbo.WTournament_Results.TRUserID,
			dbo.WUsers.Suffix,
			dbo.WUsers.UserFirstName,
			dbo.WUsers.UserLastName,
			dbo.WTournament_Points.Points AS Points,
			Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID ORDER BY dbo.WTournament_Points.Points DESC) AS RowID,
			{Table name here}.MTGPoints,
			{Table name here}.DeBriefPoints
	FROM		dbo.WTournaments
	INNER JOIN	dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID
	INNER JOIN	dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID
	INNER JOIN	dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
	WHERE		dbo.WTournaments.TournYear = @TournYear
			AND dbo.WTournaments.TournYearInc = 1
)

SELECT		TRUserID,
		Suffix,
		UserFirstName,
		UserLastName,
		SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) AS [YTD Points],
		SUM(MTGPoints) AS MTGPoints,
		SUM(DeBriefPoints) AS DeBriefPoints
FROM		cte
WHERE		Points > 0
GROUP BY	TRUserID,
		Suffix,
		UserFirstName,
		UserLastName
ORDER BY	SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) DESC

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources