Complicated Count???

Question

Complicated Count???

tadhg88

Ten Centuries

Points: 1091
More actions
March 7, 2007 at 5:25 am

#172298

Hi,
I am having some trouble with an sql statement and I’m just wondering if anyone could help me I have a ScoutingReport table that stores player-scouting info such as the matchId, coachId and playerId.
The logic is
Same ScoutedPersonId, ScoutId and MatchId is a single report regardless of type but if there is a different scout or match then it is counted as another report and each player in the count must have at least one Individual report

This is the layout of what I am trying to achieve
Times Seen               Players Count                 View
1                                44                                   View
2                                101                                 View
3                                78                                   View
4                                66                                   View
5                                35                                   View
6                                24                                  View
7                                11                                   View
8                                7                                     View
9                                8                                     View
10                              15                                   View
10+                            12                                   View

Here is what I have at the moment with any help would be appreciated.

select
sr.ScoutedPersonId,
count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) as 'IndCount'

from ScoutingReport sr
Where sr.Type = 0 -- individual reports

Group By sr.ScoutedPersonId
--Having count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) = 1
Order By sr.ScoutedPersonId

Thanks in advance
Tim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

There are 2 problems here, getting distinct views per player , then pivoting those results into 11 distinct "buckets". There are ways to dynamically pivot, but since you have a fixed number of buckets, a derived table of the 11 options can probably suffice.

Select Case

When TimeSeenBucket = -1 Then '10+'

Else Cast(TimeSeenBucket As varchar)

End As TimesSeen,

Count(*) As PlayersCount

From

-- Derived table to count times seen per player

(

Select ScoutedPersonID, Sum(TimesSeen) As TimesSeen

From

(

Select Distinct ScoutedPersonID, ScoutID, MatchID, Cast (1 As int) As TimesSeen

From ScoutingReport

) dt

Group By ScoutedPersonID

) dtPlayers

Inner Join

-- Join to derived table to generate the 11 pivot buckets

(

Select 1 As TimesSeenBucket Union All

Select 2 Union All

Select 3 Union All

Select 4 Union All

Select 5 Union All

Select 6 Union All

Select 7 Union All

Select 8 Union All

Select 9 Union All

Select 10 Union All

Select -1

) dtBuckets

On ( dtBuckets.TimesSeenBucket = dtPlayers.TimesSeen Or

(dtBuckets.TimesSeenBucket = -1 And dtPlayers.TimesSeen > 10)

)

Group By dtBuckets.TimesSeenBucket

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Another way how to do the same: build the query step by step, from "inside" - first get distinct reports only ("Reports"), then count the number of reports for each player ("Sums"), and as last step calculate how many players have what count of reports (result).

SELECT CASE WHEN Sums.TimesReported < 11 THEN CAST(Sums.TimesReported AS VARCHAR(3))

ELSE '10+' END as TimesSeen,

COUNT(*) as PlayersCount

FROM

(SELECT Reports.ScoutedPersonID, COUNT(*) as TimesReported

FROM

(SELECT DISTINCT ScoutedPersonID, ScoutID, MatchID FROM ScoutingReport) as Reports

GROUP BY Reports.ScoutedPersonID) as Sums

GROUP BY CASE WHEN Sums.TimesReported < 11 THEN CAST(Sums.TimesReported AS VARCHAR(3))

ELSE '10+' END

You will probably want to order the resultset, which is not as easy as it seems because the TimesSeen is character type, not number. If you could display numbers between 1 and 9 with a leading zero, it would be fine; otherwise you'll probably need to add another column for ordering. Anyway, I suppose you will use this query to create a view, which should be unordered, and the ordering applied when selecting from it.

tadhg88 Ten Centuries Points: 1091 More actions · Answer 3

well im impressed guys theyr both perfect thanks a million its really appreciated...... this query will just be displayed in a datagrid on page fairly basic stuff except for the query

so out of interest how exactly do u guys get to be this good at sql is it practice or alot of reading or what??? any advice or rec reading

Vladan SSC-Insane Points: 22072 More actions · Answer 4

You're welcome! For me it is mainly practice (reading BOL included here), reading the articles and posts here on SQL Server Central to get new ideas and find out which seemingly great ideas are useless in long run.

Well, and of course books help, too . Advice? Read posts that ask for help (including old, long solved questions), try to find a solution yourself, then read posted solutions and compare them with yours. Find out what is good and what bad in each solution. Create the necessary tables in your database with some sample data, and test, test, test.

tadhg88 Ten Centuries Points: 1091 More actions · Answer 5

BOL??? even tho i know im gonna kick myself when u tell me

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Vladan

SSC-Insane

Points: 22072

March 8, 2007 at 9:26 am

#693852

BOL = BooksOnLine = SQL Server Help files

tadhg88 Ten Centuries Points: 1091 More actions · Answer 7

ya i def should have know that ill def have to check in here more regularly so and try and get an education

ok cool thanks again man