Query Prob - 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)

Query Prob

Author

Topic

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2008-07-16 : 06:55:54

Hi,
I am having problems writing an SQL query in SQL Server 2005, 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
La Liga              z           4

but i need the query to produce the following results


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

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

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 07:21:06

Insert into a temporary table.
From that table you do a normal PIVOT query.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 07:21:37

May be this:-

SELECT t.Competition,
SUM(CASE WHEN Count>5 THEN 1 ELSE 0 END) AS [Count>5],
SUM(CASE WHEN Count=4 THEN 1 ELSE 0 END) AS [Count(4)],
SUM(CASE WHEN Count=3 THEN 1 ELSE 0 END) AS [Count(3)],
SUM(CASE WHEN Count=2 THEN 1 ELSE 0 END) AS [Count(2)],
SUM(CASE WHEN Count=2 THEN 1 ELSE 0 END) AS [Count(1)]
FROM (YourCurrentQuery)t
GROUP BY t.Competition

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2008-07-16 : 07:33:30

just out of interest Peso do you know if you can do a pivot on a derived table
thanks for your reply

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2008-07-16 : 07:36:14

thanks for your replay visakh that was exactly what i needed
thanks again
Tim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 07:36:58

Visakh showed you how.
By the way, were are all Club with count of 5?

E 12°55'05.25"
N 56°04'39.16"

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2008-07-16 : 08:13:55

oh ok i understand now, i thought you were talking about the new Pivot command introduced in SQL Server 2005...cheers for your help guys its very much appreciated

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources