Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Matches won & lost by Each team [Asia Cup Puzzle – II]

In this puzzle you have a table called Team. Here you have information like which team has played with which team and who won that match. You have write a T-SQL to get all teams, number of matches they have played and the number of matches won by each team.

For details please check out the sample input and the expected output below.

Sample Inputs

TeamA TeamB WonBy
Pakistan India India
Sri Lanka India India
Bangladesh India India
Sri Lanka Pakistan Sri Lanka
Bangladesh Pakistan Bangladesh
Bangladesh Sri Lanka Sri Lanka
India Sri Lanka India

Expected Output

Team MatchesPlayed MatchesWon
Bangladesh 3 1
India 4 4
Pakistan 3 0
Sri Lanka 4 2

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--


CREATE TABLE [dbo].[AsiaCup](
	[TeamA] [varchar](10) NULL,
	[TeamB] [varchar](10) NULL,
	[WonBy] [varchar](10) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Pakistan', N'India', N'India')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Sri Lanka', N'India', N'India')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Bangladesh', N'India', N'India')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Sri Lanka', N'Pakistan', N'Sri Lanka')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Bangladesh', N'Pakistan', N'Bangladesh')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Bangladesh', N'Sri Lanka', N'Sri Lanka')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'India', N'Sri Lanka', N'India')
GO

--

SOLUTION – 1


--

SELECT tbl.Team, COUNT(1) MatchesPlayed, SUM(CASE WHEN team = wonby THEN 1 ELSE 0 end) MatchesWon
FROM 
(
	SELECT a.teamA team, a.wonby
	FROM    asiacup a 
	UNION all
	select b.teamb, b.wonby FROM asiacup b
) tbl
GROUP BY tbl.team


--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements