Post reply

Query Help

  • July 16, 2008 at 3:56 am

    #195292

    Hi,

    I am having problems writing an SQL query to produce the following results

    the count(4) represents clubs with a count of 4 etc

    Competition Count >5 Count(4) Count(3) Count(2) Count(1)

    Premier 8 3 5 3 1

    La Liga 7 4 2 5 2

    Serie A 6 2 4 4 3

    i have written the following query

    Select comp.Name as 'Competition', c.Name, Count(c.ID) as 'Count'

    From ScoutingLeagues sl

    Join Competition comp on comp.ID = sl.CompetitionID

    Join Seasons sea on sea.ID = 148

    Join ClubLeagueMapping clm on clm.CompetitionID = comp.ID

    Join Club c on c.ID = clm.ClubID

    Join Squad s on s.ClubID = c.ID

    Join ScoutingTeam st on st.SquadID = s.ID

    Join ScoutingReport sr on sr.ID Between st.MinScoutReportID And MaxScoutReportID And sr.ReportDate Between sea.StartDate And sea.EndDate

    Where sl.ScoutingLeagueType = 0

    And clm.DateEntered <= sea.StartDate -- entered league before season start

    And (clm.DateLeft is null or clm.DateLeft >= sea.EndDate) -- still in league or left before season end

    And comp.ID = 0

    Group By comp.Name, c.Name, c.ID

    which returns the following results

    Competition Club Count

    Premier a 8

    Premier b 7

    Premier c 6

    Premier d 5

    so im just wondering if anyone has any idea how to count the number of clubs that have a specified count, i have tried a derived table but cant figure it out,

    thanks in advance

    Tim

  • July 16, 2008 at 4:30 am

    #843660

    Competition Club Count

    Premier a 8

    Premier b 7

    Premier c 6

    Premier d 5

    do you mean to say that you would like to see all the clubs with count 8, all clubs with count 7 and so on?? 🙂

  • July 16, 2008 at 4:42 am

    #843667

    sorry no those are the results i have displaying at the moment, i have a count for each club but now i need to group the clubs by league and just display the league name and the amount of clubs in each league that have a count of 5 or more, a count ot 4, 3 etc displayed like this

    Competition Count >5 Count(4) Count(3) Count(2) Count(1)

    Premier 8 3 5 3 1

    La Liga 7 4 2 5 2

    Serie A 6 2 4 4 3

    thanks

    Tim

  • July 21, 2008 at 12:26 am

    #845552

    Try Using PIVOT with SUM as as Aggrigate Function.

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • July 21, 2008 at 4:15 am

    #845628

    thanks alot for your help i managed to get it working

    Tim

Viewing 5 posts - 1 through 4 (of 4 total)

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