pvt table 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 2005 Forums

Transact-SQL (2005)

pvt table sum

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2009-11-25 : 14:07:23

i have a working pivot table that gives me everything i need except one thing...i am hoping someone can give me a solution

the output gives me totals by month that are added up to give me a yearly total ...however...what i want is not the yearly total but the TOTAL of the 8 highest months

here is the part of the stored procedure that does the SUM
IsNull([1], 0) + IsNull([2], 0) + IsNull([3], 0) + IsNull([4], 0) + IsNull([5], 0) + IsNull([6], 0) + IsNull([7], 0) + IsNull(

, 0) + IsNull([9], 0) + IsNull([10], 0) + IsNull([11],0) + IsNull([12],0) AS 'TournPoints'

here is the complete stored procedure

ALTER PROCEDURE [dbo].[WPRO_Get_TOURNAMENT_MATRIX]
(
@TournYear int
)

AS

SELECT UserID, USERLASTNAME + ' ' + SUFFIX AS 'Angler',
IsNull([1], 0) AS [Jan],
IsNull([2], 0) AS [Feb],
IsNull([3], 0) AS [Mar],
IsNull([4], 0) AS [Apr],
IsNull([5], 0) AS [May],
IsNull([6], 0) AS [Jun],
IsNull([7], 0) AS [Jul],
IsNull(

, 0) AS [Aug],
IsNull([9], 0) AS [Sep],
IsNull([10], 0) AS [Oct],
IsNull([11], 0) AS [Nov],
IsNull([12], 0) AS [Dec],
IsNull([1], 0) + IsNull([2], 0) + IsNull([3], 0) + IsNull([4], 0) + IsNull([5], 0) + IsNull([6], 0) + IsNull([7], 0) + IsNull(

, 0) + IsNull([9], 0) + IsNull([10], 0) + IsNull([11],0) + IsNull([12],0) AS 'TournPoints'
FROM ccbass.dbo.Tournament_Results_for_PVT PIVOT (max(TRpoints) FOR tournmonth IN ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12])) AS pvt
where Tournyear = @TournYear
order by Tournpoints desc,Userlastname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 02:40:10

do u want individual month totals as well or just 8 month aggregate?

joemama
Posting Yak Master

113 Posts

Posted - 2009-11-28 : 00:48:52

I need both

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-28 : 01:50:41

[code]
select UserID,Angler,
SUM(CASE WHEN Seq<=8 THEN TRpoints ELSE 0 END) AS 8mnthaggre,
MAX(CASE WHEN tournmonth=1 THEN TRpoints ELSE NULL END) AS Jan,
MAX(CASE WHEN tournmonth=2 THEN TRpoints ELSE NULL END) AS Feb,
......
MAX(CASE WHEN tournmonth=12 THEN TRpoints ELSE NULL END) AS Dec
from
(
select UserID, USERLASTNAME + ' ' + SUFFIX AS 'Angler',
tournmonth,
SUM(TRpoints) AS TRpoints,
ROW_NUMBER() OVER(PARTITION BY UserID, USERLASTNAME + ' ' + SUFFIX ORDER BY SUM(TRpoints) DESC) AS Seq
from ccbass.dbo.Tournament_Results_for_PV
where Tournyear = @TournYear
group by UserID, USERLASTNAME + ' ' + SUFFIX,tournmonth
)t
GROUP BY UserID, Angler
[/code]

joemama
Posting Yak Master

113 Posts

Posted - 2009-11-30 : 17:55:34

YOU ROCK!
this is perfect...thanks

quote:

Originally posted by visakh16


select UserID,Angler,
SUM(CASE WHEN Seq<=8 THEN TRpoints ELSE 0 END) AS 8mnthaggre,
MAX(CASE WHEN tournmonth=1 THEN TRpoints ELSE NULL END) AS Jan,
MAX(CASE WHEN tournmonth=2 THEN TRpoints ELSE NULL END) AS Feb,
......
MAX(CASE WHEN tournmonth=12 THEN TRpoints ELSE NULL END) AS Dec
from
(
select UserID, USERLASTNAME + ' ' + SUFFIX AS 'Angler',
tournmonth,
SUM(TRpoints) AS TRpoints,
ROW_NUMBER() OVER(PARTITION BY UserID, USERLASTNAME + ' ' + SUFFIX ORDER BY SUM(TRpoints) DESC) AS Seq
from ccbass.dbo.Tournament_Results_for_PV
where Tournyear = @TournYear
group by UserID, USERLASTNAME + ' ' + SUFFIX,tournmonth
)t
GROUP BY UserID, Angler

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources