SQL Server Performance Forum – Threads Archive

Difficult count??

Hi i am tryin to count the different types of injuries that players have had and display them in a stacked column chart i can manage
to count the total amount of injuries for each player but im not sure how to go about countin the different types in the same query
select IsNull(firstName+ ‘ ‘,”) + IsNull(MiddleName+ ‘ ‘,”) + IsNull(LastName,”) as Name, count(i.PersonId) as ‘Games Missed’
from injury i
Left Join Person p on i.PersonID = p.ID
Left Join teams t on t.PlayerId = p.ID
Left Join Match m on m.ID = t.MatchID
Left Join SquadPlayerMapping spm on spm.PlayerID = i.PersonID
Where m.Date between i.Date and i.DateRecovered
And spm.SquadId = 5
And ((i.MechanismOfInjury in (-1)) or (-1 in (-1)))
group by i.PersonId, firstName, MiddleName, LastName
the i.MechanismOfInjury in -1 represents all the injuries bein counted
but the different options that need to be counted are (13), (14), (15, 16)
i wud really appreciate any help that can be offered thanks in advance

Thanks for the query. Can what post how the final result should look like? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail

NamePersonIDMechanismOfInjury
Ricardo Gardner1215
Ricardo Gardner1216
Gary Speed815
Ricardo Gardner1216
Ricardo Gardner1214
Stelios Giannakopoulos915
Gary Speed816
Stelios Giannakopoulos914
Jay-Jay Okocha1116
Nicky Hunt315
Abdoulaye Diagne-Faye2515
Jay-Jay Okocha1113
Radhi Jaidi1416
Fabrice Fernandes3116
Gary Speed813
Abdoulaye Diagne-Faye2514
Radhi Jaidi1416
Ricardo Gardner1213
Jay-Jay Okocha1114
im not sure if this is enough data let me know
I need it to look something like this these numbers at the bottom are correct NameTraumaOveruseIllness
Nicky Hunt100
Gary Speed201
Stelios Giannakopoulos110
Jay-Jay Okocha111
Ricardo Gardner311
Radhi Jaidi200
Abdoulaye Diagne-Faye110
Fabrice Fernandes100

How do you categorise Trauma, Overuse and Illness? Look for cross-tab reports in sql server help file
Madhivanan Failing to plan is Planning to fail

It looks like cross-tab repoprts season just started. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Maybe we could put faq somewhere.

There is already one. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Go to www.sqlteam.com. Go to articles and search for cross-tab, cross tab, or pivot table. You’ll get some good articles. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

>>Maybe we could put faq somewhere. Yes. That is good idea [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Derrick, nice to see you back [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

thanks alot guys i appreciate it

]]>