|
ganeshkumar08
Posting Yak Master
187 Posts |
 Posted - 2008-06-16 : 05:54:06
|
| Hi all,This is my result set. And before select dbo.fn_ResolveClubID(clubid) as 'Team', dbo.fn_ResolveMatchID(matchid) as 'Match', convert (varchar, max(matchDate), 111) as 'Date',-- dbo.fn_ResolveTimeValue(@SessionID, max(TimeOnPitch)) as 'Time On Pitch', -- For Extrpolation case when (sum(TimeonPitch) <> sum(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, sum(CopyTimeonPitch)) else dbo.fn_ResolveTimeValue(@SessionID, sum(totalperiod)) end as 'Time On Pitch', cast(cast(sum(totalPassCount) as int) as int) as 'Passes', cast(cast(sum(SuccessfulPasses) as int) as int) as 'Successful Passes', cast(cast(sum((totalPassCount - SuccessfulPasses)) as int) as int) as 'Unsuccessful Passes', cast(ISNULL(cast((sum(SuccessfulPasses)/NULLIF(sum(totalPassCount), 0)) * 100 as numeric(7,1)), 0) as numeric(7,1)) as '% successful passes', cast(ISNULL(cast(((sum(totalPassCount) - sum(SuccessfulPasses))/NULLIF(sum(totalPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as '% unsuccessful passes', cast(cast(sum(forwardPassCount) as int) as int) as 'Forwards', cast(cast(sum(forwardPassSuccess) as int) as int) as 'Forwards Successful', cast(cast(sum((forwardPassCount - forwardPassSuccess)) as int) as int) as 'Forwards Unsuccessful', cast(ISNULL((sum(forwardPassSuccess)/NULLIF(avg(forwardPassCount), 0)) * 100, 0.0) as numeric(7,1) ) as 'Forwards % successful', cast(ISNULL(cast(((sum(forwardPassCount) - sum(forwardPassSuccess))/NULLIF(sum(forwardPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as 'Forwards % unsuccessful', cast(cast(sum(backwardPassCount) as int) as int) as 'Backwards', cast(cast(sum(backwardPassSuccess) as int) as int) as 'Backwards Successful', cast(cast((sum(backwardPassCount) - sum(backwardPassSuccess)) as int) as int) as 'Backwards Unsuccessful', cast(ISNULL((sum(backwardPassSuccess)/NULLIF(avg(backwardPassCount), 0)) * 100, 0.0) as numeric(7,1)) as 'Backwards % successful', cast(ISNULL(cast(((sum(backwardPassCount) - sum(backwardPassSuccess))/NULLIF(sum(backwardPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as 'Backwards % unsuccessful', cast(cast(sum(sidewaysPassCount) as int) as int) as 'Sideways', cast(cast(sum(sidewaysPassSuccess) as int) as int) as 'Sideways Successful', cast(cast(sum((sidewaysPassCount - sidewaysPassSuccess)) as int) as int) as 'Sideways Unsuccessful', cast((sum(sidewaysPassSuccess)/NULLIF(sum(sidewaysPassCount), 0)) * 100 as numeric(7,1) ) as 'Sideways % successful', cast(ISNULL(cast((sum((sidewaysPassCount - sidewaysPassSuccess))/NULLIF(sum(sidewaysPassCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as 'Sideways % unsuccessful', cast(sum(successfulFirstTime + unsuccessfulFirstTime) as int) as 'First Time Passes', cast(sum(successfulFirstTime) as int) as 'First Time Complete', cast(sum(unsuccessfulFirstTime) as int) as 'First Time Incomplete', cast(ISNULL(cast(((sum(successfulFirstTime)/NULLIF((cast(sum(unsuccessfulFirstTime) as int) + cast(sum(successfulFirstTime) as int)), 0)) * 100) as numeric(7,1)), 0.0) as numeric(7,1)) as '% First Time Complete', cast(ISNULL(cast((sum(unsuccessfulFirstTime)/NULLIF((cast(sum(unsuccessfulFirstTime) as int) + cast(sum(successfulFirstTime) as int)), 0) * 100) as numeric(7,1)), 0.0) as numeric(7,1)) as '% First Time Incomplete', --dbo.fn_ResolvePlayerID(Select Top 1 PlayerID From @REsu) as 'Top Passer', Max(TopPasserNumber) as [Top Passer Number], Max(TopReceiverNumber) as [Top Receiver Number], cast(cast(cast(sum(headersCount) as int) as int) as int) as 'Headers', cast(ISNULL(cast((sum(SuccessfulHeaders)/NULLIF(sum(headersCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as '% successful headers', cast(ISNULL(cast((sum((headersCount - SuccessfulHeaders))/NULLIF(sum(headersCount), 0)) * 100 as numeric(7,1)), 0.0) as numeric(7,1)) as '% unsuccessful headers', cast(cast(sum(passReceivedCount) as int) as int) as 'Balls Received', cast(sum((InterceptionCount + InterceptionsAfterTackle)) as int) as 'Interceptions', cast(sum(numberOfTouches) as int) as '# touches', cast(sum(numberOfPossessions) as int) as '# Possessions', cast(ISNULL(cast((cast(NULLIF(sum(numberOfTouches), 0) as numeric(7,1))/cast(NULLIF(sum(numberOfPossessions),0) as numeric(7,1))) as numeric(7,1)), 0.0) as numeric(7,1)) as 'Avg Touches', cast(ISNULL(cast(sum(TimeOnBall)/cast(NULLIF(sum(numberOfPossessions), 0) as numeric(7,1)) as numeric(7,1)), 0.0) as numeric(7,1)) as 'Avg Time in Poss', cast(sum(shortPassCount) as int) as 'Short Passes', cast(sum(mediumPassCount) as int) as 'Medium Passes', cast(sum(longPassCount) as int) as 'Long Passes' -- For Extrapolation ,case when (sum(TimeonPitch) < sum([totalperiod])) then '1' else '0' end as 'ExtraPolated' from @results Res where Res.ClubID in (Select ClubID From TrendSelected_Teams Where SessionID = @SessionID) and ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or (@excludeGK = -1)) group by Res.clubid, Res.matchidBefore displaying the above columns there are many calculation, updation's etc. After doing all that i am displaying the above columns.Here i am showing the results Team wise.Now i need to merge one more column called player name.So, already the result set has Group By Res.clubid, Res.matchid so i cant go for grouping by Playerid, i need result only by ClubID wise, how can i add one column which should display PlayerID or PlayerNameThanksGaneshSolutions are easy. Understanding the problem, now, that's the hard part |
|
Help me in adopting new column in query