sql query doubt - 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 2012 Forums

Transact-SQL (2012)

sql query doubt

Author

Topic

vijayabalajicse
Starting Member

5 Posts

Posted - 2013-10-05 : 10:01:43

<pre>
sql query for above

Team1 Team2 winner
A B A
A B A
B C C
A B B
A C C
A D D
A D -
B D -

Number of match each team played,
Number of win/lose of each team,
Number of match drawn by each team

</pre>

jethrow
Starting Member

37 Posts

Posted - 2013-10-05 : 14:30:31

[code]WITH Games AS (
SELECT Team1 AS Team,
CASE WHEN Team1=winner THEN 1 ELSE 0 END AS Win,
CASE WHEN Team1<>winner AND winner<>'-' THEN 1 ELSE 0 END AS Loss,
CASE WHEN winner='-' THEN 1 ELSE 0 END AS Draw
FROM YourTable
UNION ALL
SELECT Team2 AS Team,
CASE WHEN Team2=winner THEN 1 ELSE 0 END AS Win,
CASE WHEN Team2<>winner AND winner<>'-' THEN 1 ELSE 0 END AS Loss,
CASE WHEN winner='-' THEN 1 ELSE 0 END AS Draw
FROM YourTable
)
SELECT Team,
Count(*) AS GamesPlayed,
Sum(Win) AS Wins,
Sum(Loss) AS Losses,
Sum(Draw) AS Drawn
FROM Games
GROUP BY Team[/code]

Microsoft SQL Server Noobie

vijayabalajicse
Starting Member

5 Posts

Posted - 2013-10-05 : 23:19:26

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.

jethrow
Starting Member

37 Posts

Posted - 2013-10-05 : 23:45:33

Ok? So you went so far as to post the error message - did you look into it at all? It's working as expected on SQL Svr 2012. You could try it rewritten as:

SELECT	Team,
	Count(*) AS GamesPlayed,
	Sum(Win) AS Wins,
	Sum(Loss) AS Losses,
	Sum(Draw) AS Drawn
FROM (
	SELECT	Team1 AS Team,
		CASE WHEN Team1=winner THEN 1 ELSE 0 END AS Win,
		CASE WHEN Team1<>winner AND winner<>'-' THEN 1 ELSE 0 END AS Loss,
		CASE WHEN winner='-' THEN 1 ELSE 0 END AS Draw
	FROM	YourTable
UNION ALL
	SELECT	Team2 AS Team,
		CASE WHEN Team2=winner THEN 1 ELSE 0 END AS Win,
		CASE WHEN Team2<>winner AND winner<>'-' THEN 1 ELSE 0 END AS Loss,
		CASE WHEN winner='-' THEN 1 ELSE 0 END AS Draw
	FROM	YourTable
) AS Games
GROUP	BY Team

Microsoft SQL Server Noobie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-06 : 03:46:56

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Team1 VARCHAR(10) NOT NULL,
Team2 VARCHAR(10) NOT NULL,
Winner VARCHAR(10) NOT NULL
);

INSERT @Sample
(
Team1,
Team2,
Winner
)
VALUES ('A', 'B', 'A'),
('A', 'B', 'A'),
('B', 'C', 'C'),
('A', 'B', 'B'),
('A', 'C', 'C'),
('A', 'D', 'D'),
('A', 'D', '-'),
('B', 'D', '-');

-- SwePeso solution
SELECT w.Team,
SUM(w.Wins) AS Wins,
SUM(w.Loss) AS Loss,
SUM(w.Draws) AS Draws
FROM @Sample AS s
CROSS APPLY (
VALUES (Team1, CASE WHEN Team1 = Winner THEN 1 ELSE 0 END, CASE WHEN Winner NOT IN ('-', Team1) THEN 1 ELSE 0 END, CASE WHEN Winner = '-' THEN 1 ELSE 0 END),
(Team2, CASE WHEN Team2 = Winner THEN 1 ELSE 0 END, CASE WHEN Winner NOT IN ('-', Team2) THEN 1 ELSE 0 END, CASE WHEN Winner = '-' THEN 1 ELSE 0 END)
) AS w(Team, Wins, Loss, Draws)
GROUP BY w.Team
ORDER BY w.Team;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources