How to SUM Away victory in this

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)

How to SUM Away victory in this

Author

Topic

gerten
Starting Member

17 Posts

Posted - 2007-09-23 : 00:15:31

I got this table and want to calculate Played V O F Score Points

MatchId     Matchstart              LagIdHome   LagIdAway  ResultHome    ResultAway
----------- ----------------------- ----------- ----------- ------------- -------------
700201001   2007-09-08 10:00:00.000 33768       159120      16            4
700201002   2007-09-08 15:00:00.000 33744       159120      16            6
700201003   2007-09-08 11:00:00.000 33894       33407       16            4
700201004   2007-09-08 14:00:00.000 33874       33407       11            9
700201005   2007-09-08 11:00:00.000 33737       32186       10            10
700201006   2007-09-08 16:20:00.000 30896       32186       15            5
700201007   2007-09-08 11:00:00.000 33286       42031       11            9
700201008   2007-09-08 15:00:00.000 33290       42031       14            5
700201009   2007-09-08 11:00:00.000 33628       33722       13            7
700201010   2007-09-08 15:00:00.000 33684       33722       15            5
700201011   2007-09-08 11:00:00.000 43635       33705       9             11
700201012   2007-09-08 15:00:00.000 31346       33705       16            4
700201013   2007-09-15 11:00:00.000 159120      33768       8             12
700201014   2007-09-15 16:00:00.000 33407       33768       11            9
700201015   2007-09-15 11:00:00.000 33874       33744       9             11
700201016   2007-09-15 13:00:00.000 33894       33744       14            6
700201017   2007-09-15 10:00:00.000 32186       33737       12            8
700201018   2007-09-15 16:00:00.000 42031       33737       13            7
700201019   2007-09-15 11:00:00.000 33290       30896       9             11
700201020   2007-09-15 15:40:00.000 33286       30896       10            10
700201021   2007-09-15 11:00:00.000 33722       33628       9             10
700201022   2007-09-15 16:00:00.000 33705       33628       12            8
700201023   2007-09-15 11:00:00.000 31346       33684       8             12
700201024   2007-09-15 15:00:00.000 43635       33684       16            4

My SQL so far dont get right if TeamId has an awaymatch and win.

SELECT Team.Name, Team.GroupId, COUNT(*) AS S, 
SUM(CASE WHEN PointsFor > PointsAgainst  THEN 1 ELSE 0 END) AS V, 
SUM(CASE WHEN PointsFor = PointsAgainst THEN 1 ELSE 0 END) AS O, 
SUM(CASE WHEN PointsFor < PointsAgainst THEN 0 ELSE 1 END) AS F, 
STR(SUM(view1.PointsFor), 3, 0) + '-' + CONVERT(varchar(3), 
SUM(view1.PointsAgainst)) AS TOTAL, 
SUM(CASE WHEN PointsFor > PointsAgainst THEN 2 ELSE 
    CASE WHEN PointsFor = PointsAgainst THEN 1 ELSE 0 END END) AS P
FROM 
(SELECT MatchId, LagIdHemma AS TeamId, 
ResultatHome AS PointsFor, ResultatAway AS PointsAgainst
 FROM Match
      UNION ALL
	  SELECT MatchId, LagIdBorta AS TeamId, 
ResultatAway AS PointsFor, ResultatHome AS PointsAgainst
	  FROM Match AS Match_1) AS view1 INNER JOIN
      Team ON view1.TeamId = Team.TeamId
GROUP BY view1.TeamId, Team.Name, Team.GroupId
ORDER BY Team.GroupId, P DESC, Total DESC, S DESC

Tabel Team:


30896	Sundbybergs IK	2
31346	Turebergs IF	3
32186	Örta IF	        2
33286	Domnarvets BS	2
33290	Ludvika BK	2
33407	BK Glam	        1
33628	BK Amiki	3
33684	Stureby BK	3
33705	BK Brio	        3
33722	BK Stallis	3
33737	Uppsala BC90	2
33744	BK Bågen	1
33768	Wåxnäs BC	1
33874	BK Kaskad	1
33894	BK Örnen	1
42031	LBK Hudik	2
43635	Djurgårdens IF	3
159120	IFK Norrköping	1

Gerten

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 03:23:53

V O F is (Wins, Ties, Losses) for those not familiar with Swedish abbreviations.
I also tool the liberty to "internationalize" the table structure

DECLARE	@Sample TABLE (GameID INT, GameStart SMALLDATETIME, HomeTeamID INT, AwayTeamID INT, ResultHome TINYINT, ResultAway TINYINT)

INSERT	@Sample
SELECT	700201001, '2007-09-08 10:00', 33768, 159120, 16, 4 UNION ALL
SELECT	700201002, '2007-09-08 15:00', 33744, 159120, 16, 6 UNION ALL
SELECT	700201003, '2007-09-08 11:00', 33894, 33407, 16, 4 UNION ALL
SELECT	700201004, '2007-09-08 14:00', 33874, 33407, 11, 9 UNION ALL
SELECT	700201005, '2007-09-08 11:00', 33737, 32186, 10, 10 UNION ALL
SELECT	700201006, '2007-09-08 16:20', 30896, 32186, 15, 5 UNION ALL
SELECT	700201007, '2007-09-08 11:00', 33286, 42031, 11, 9 UNION ALL
SELECT	700201008, '2007-09-08 15:00', 33290, 42031, 14, 5 UNION ALL
SELECT	700201009, '2007-09-08 11:00', 33628, 33722, 13, 7 UNION ALL
SELECT	700201010, '2007-09-08 15:00', 33684, 33722, 15, 5 UNION ALL
SELECT	700201011, '2007-09-08 11:00', 43635, 33705, 9, 11 UNION ALL
SELECT	700201012, '2007-09-08 15:00', 31346, 33705, 16, 4 UNION ALL
SELECT	700201013, '2007-09-15 11:00', 159120, 33768, 8, 12 UNION ALL
SELECT	700201014, '2007-09-15 16:00', 33407, 33768, 11, 9 UNION ALL
SELECT	700201015, '2007-09-15 11:00', 33874, 33744, 9, 11 UNION ALL
SELECT	700201016, '2007-09-15 13:00', 33894, 33744, 14, 6 UNION ALL
SELECT	700201017, '2007-09-15 10:00', 32186, 33737, 12, 8 UNION ALL
SELECT	700201018, '2007-09-15 16:00', 42031, 33737, 13, 7 UNION ALL
SELECT	700201019, '2007-09-15 11:00', 33290, 30896, 9, 11 UNION ALL
SELECT	700201020, '2007-09-15 15:40', 33286, 30896, 10, 10 UNION ALL
SELECT	700201021, '2007-09-15 11:00', 33722, 33628, 9, 10 UNION ALL
SELECT	700201022, '2007-09-15 16:00', 33705, 33628, 12, 8 UNION ALL
SELECT	700201023, '2007-09-15 11:00', 31346, 33684, 8, 12 UNION ALL
SELECT	700201024, '2007-09-15 15:00', 43635, 33684, 16, 4

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 03:28:36

[code]-- Show the expected output
SELECT TeamID,
COUNT(*) AS Games,
SUM(theWins) AS [Wins],
SUM(theTies) AS [Ties],
SUM(theLosses) AS [Losses],
2 * SUM(theWins) + SUM(theTies) AS [Points]
FROM (
SELECT HomeTeamID AS TeamID,
CASE WHEN ResultHome > ResultAway THEN 1 ELSE 0 END AS theWins,
CASE WHEN ResultHome = ResultAway THEN 1 ELSE 0 END AS theTies,
CASE WHEN ResultHome < ResultAway THEN 1 ELSE 0 END AS theLosses
FROM @Sample

UNION ALL

SELECT AwayTeamID,
CASE WHEN ResultHome < ResultAway THEN 1 ELSE 0 END,
CASE WHEN ResultHome = ResultAway THEN 1 ELSE 0 END,
CASE WHEN ResultHome > ResultAway THEN 1 ELSE 0 END
FROM @Sample
) AS d
GROUP BY TeamID
ORDER BY 2 * SUM(theWins) + SUM(theTies) DESC[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 03:48:40

Moderator, please move this topic from "WORKING SCRIPT LIBRARY" to proper T-SQL 2005 or T-SQL 2000 forum.

Anyone?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 08:22:45

Not only this thread there are lot of threads to be moved to respective forums

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 10:18:35

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources