Calculate the league standings

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)

Calculate the league standings

Author

Topic

Raipaia
Starting Member

4 Posts

Posted - 2010-11-26 : 03:40:22

Hi, I need a help with the query that should calculate the league standings. I have a table of matches and there is the id of host team, the id of the guest team, how many goals the host team scored and how many goals the guest team scored. I need to go through the whole table and find out how many matches every team played, how many matches every team won, lost and tied (obv. depends on the number of scored goal in the match). The result should be something like:

ID | Matches played | Matches won | Matches tied | Matches lost

I have actually no idea how to even start with that, could you give me some hint please? Thank you

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-26 : 04:06:13

Might be this -


CREATE TABLE #Matches ( HostID VARCHAR(10), GuestID VARCHAR(10), HostScore INT, GuestScore INT)
GO
INSERT INTO #Matches
SELECT 'H1','G1','4','3'
UNION ALL
SELECT 'H1','G1','3','2'
UNION ALL
SELECT 'H2','G1','2','6'
UNION ALL
SELECT 'H2','G1','4','3'
UNION ALL
SELECT 'H2','G5','0','1'
UNION ALL
SELECT 'H3','G6','2','0'
UNION ALL
SELECT 'H3','G7','1','1'
UNION ALL
SELECT 'H4','G7','0','3'
UNION ALL
SELECT 'H4','G7','3','5'
UNION ALL
SELECT 'H5','G8','2','2'
UNION ALL
SELECT 'H6','G8','1','1'
GO

SELECT ID, COUNT(*) MatchPlayed, 
SUM ( CASE WHEN MatchResult = 'Won' THEN 1 ELSE 0 END ) MatchWon, 
SUM ( CASE WHEN MatchResult = 'Tied' THEN 1 ELSE 0 END ) MatchTied, 
SUM ( CASE WHEN MatchResult = 'Lost' THEN 1 ELSE 0 END ) MatchLost
FROM 
(
	SELECT HostID AS ID, 
	CASE WHEN HostScore>GuestScore THEN 'Won'
	WHEN HostScore < GuestScore THEN 'Lost'
	WHEN HostScore = GuestScore THEN 'Tied'
	END AS MatchResult
	FROM #Matches

	UNION ALL	
	SELECT GuestID AS ID, 
	CASE WHEN HostScore>GuestScore THEN 'Won'
	WHEN HostScore < GuestScore THEN 'Lost'
	WHEN HostScore = GuestScore THEN 'Tied'
	END AS MatchResult
	FROM #Matches

) A
GROUP BY ID

Vaibhav T

If I cant go back, I want to go fast...

Raipaia
Starting Member

4 Posts

Posted - 2010-11-26 : 04:40:55

Wooow, great job, really helpful! Thank you. I have one more stupid question :-) Why is there 'A' in front of the GROUP BY? Thank you once more!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-26 : 04:46:26

You're Welcome

A is the alias for the Sub-Query Which is in Brackets Before A.

Vaibhav T

If I cant go back, I want to go fast...

Raipaia
Starting Member

4 Posts

Posted - 2010-11-26 : 04:50:20

OK, thanks a lot :)

Raipaia
Starting Member

4 Posts

Posted - 2010-11-26 : 09:53:23

Well, I found another problem :)

I have a table of teams, so I did something like:

SELECT t.name, COUNT(*) MatchPlayed,
SUM ( CASE WHEN MatchResult = 'Won' THEN 1 ELSE 0 END ) MatchWon,
SUM ( CASE WHEN MatchResult = 'Tied' THEN 1 ELSE 0 END ) MatchTied,
SUM ( CASE WHEN MatchResult = 'Lost' THEN 1 ELSE 0 END ) MatchLost
FROM
(
SELECT HostID AS ID,
CASE WHEN HostScore>GuestScore THEN 'Won'
WHEN HostScore < GuestScore THEN 'Lost'
WHEN HostScore = GuestScore THEN 'Tied'
END AS MatchResult
FROM #Matches

UNION ALL
SELECT GuestID AS ID,
CASE WHEN HostScore>GuestScore THEN 'Won'
WHEN HostScore < GuestScore THEN 'Lost'
WHEN HostScore = GuestScore THEN 'Tied'
END AS MatchResult
FROM #Matches

) A INNER JOIN Teams t ON A.ID = t.ID
GROUP BY t.name

It´s working but the problem is that if I there is a team that didn´t play any match, it´s not in the result of query. But I would need this team to be there and all atributes in the result should be filled with zero.

Something like:
Name | Played | Won | Tied | Lost
Boston 0 0 0 0

Could you help me with that? Thank you

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 10:42:27

SELECT t.Name, COUNT(*) as MatchPlayed,
SUM ( IsNull(WON,0) ) as MatchWon,
SUM ( isNull(TIED,0) ) as MatchTied,
SUM ( isNull(LOST,0) ) as MatchLost
FROM Teams t LEFT JOIN
(
SELECT HostID AS ID,
CASE WHEN HostScore>GuestScore THEN 1 else 0 end as WON,
CASE WHEN HostScore < GuestScore THEN 1 else 0 end as LOST,
CASE WHEN HostScore = GuestScore THEN 1 else 0 end as TIED
END AS MatchResult
FROM #Matches

UNION ALL
SELECT GuestID AS ID,
CASE WHEN HostScore>GuestScore THEN 1 else 0 end as WON,
CASE WHEN HostScore < GuestScore THEN 1 else 0 end as LOST,
CASE WHEN HostScore = GuestScore THEN 1 else 0 end as TIED

END AS MatchResult
FROM #Matches

) A ON t.ID = A.ID
GROUP BY t.name

Poor planning on your part does not constitute an emergency on my part.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-26 : 11:22:54

I worked on a Database like this. I always scratched my head as to whether having the Teams in the Matches table was a violation of 1nf. Technically the Relationship between Teams and Results is a many to 1 relationship.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources