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.
Author |
Topic |
mosiki
Starting Member
12 Posts |
Posted - 2013-06-24 : 11:01:57
|
HiWhen I run the below code i get the result set:Team Home Games Home Win Home Draw Home Loss Home Pts Away Games Away Win Away Draw Away Loss Away PtsMan United 19 16 0 3 48 19 3 0 16 48Man City 19 14 3 2 45 19 2 3 14 45Everton 19 12 6 1 42 19 1 6 12 42Chelsea 19 12 5 2 41 19 2 5 12 41Arsenal 19 11 5 3 38 19 3 5 11 38Tottenham 19 11 5 3 38 19 3 5 11 38West Ham 19 9 6 4 33 19 4 6 9 33Liverpool 19 9 6 4 33 19 4 6 9 33Norwich 19 8 7 4 31 19 4 7 8 31West Brom 19 9 4 6 31 19 6 4 9 31Stoke 19 7 7 5 28 19 5 7 7 28Newcastle 19 9 1 9 28 19 9 1 9 28Swansea 19 6 8 5 26 19 5 8 6 26Southampton 19 6 7 6 25 19 6 7 6 25Fulham 19 7 3 9 24 19 9 3 7 24Sunderland 19 5 8 6 23 19 6 8 5 23Reading 19 4 8 7 20 19 7 8 4 20Aston Villa 19 5 5 9 20 19 9 5 5 20Wigan 19 4 6 9 18 19 9 6 4 18QPR 19 2 8 9 14 19 9 8 2 14The problem is the Away data is actually the home data again. Its not pulling in the away data for each team separate to the home data.SELECT Soccer_Base.dbo.Results.HomeTeam as Team ,COUNT(Soccer_Base.dbo.Results.HomeTeam) as [Home Games] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Win] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Draw] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Loss] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END)*3 + SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Pts] ,COUNT(Soccer_Base.dbo.Results.AwayTeam) as [Away Games] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Away Win] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Away Draw] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Away Loss] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END)*3 + SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Away Pts] FROM Soccer_Base.dbo.Results WHERE Season = '2012-13' AND Competition = 'Premier League' GROUP BY Soccer_Base.dbo.Results.HomeTeam ORDER BY [Home Pts] Desc For eg. Man United are Home Games 19, Home Win 16, Home Draw 0, Home Loss 3. This is then repeated for the away stats. I tried a UNION command but it lists the away data as separate rows so that i have two rows of data per team.Any ideas on how to get it all on one row?ThanksMosiki. |
|
mhorseman
Starting Member
44 Posts |
Posted - 2013-06-24 : 12:23:08
|
Hi,Once you've got your Away data select working OK, UNION it to the Home data, then wrap a Select and Group by around the whole thing to add the Home and Away data together. Here's an example of what I mean (I made up some data to try to illustrate): drop table #teamcreate table #team(team char(10))insert into #teamselect 'Arsenal' union select 'Bradford' union select 'Chelsea' union select 'Derby' drop table #rescreate table #res(hometeam char(10),awayteam char(10),homeft int,awayft int)insert into #resselect 'Arsenal','Bradford',3,1 unionselect 'Arsenal','Chelsea',0,0 unionselect 'Arsenal','Derby',1,2 unionselect 'Bradford','Chelsea',2,2select team,sum(P) P,sum(hw) HW,sum(hd) HD,sum(hl) HL,sum(aw) AW,sum(ad) AD,sum(al) AL,((sum(hw) + sum(aw)) * 3) + (sum(hd) + sum(ad)) Pts from(select t.team,count(hometeam) P,isnull(sum(case whenhomeft > awayft then 1 end),0) HW,isnull(sum(case whenhomeft = awayft then 1 end),0) HD,isnull(sum(case whenhomeft < awayft then 1 end),0) HL,0 aw,0 ad,0 alfrom #team t left join #res r on t.team = r.hometeamgroup by t.teamunionselect t.team,count(awayteam) P,0 hw,0 hd,0 hl,isnull(sum(case whenhomeft < awayft then 1 end),0) AW,isnull(sum(case whenhomeft = awayft then 1 end),0) AD,isnull(sum(case whenhomeft > awayft then 1 end),0) ALfrom #team t left join #res r on t.team = r.awayteamgroup by t.team) zgroup by teamYou may want to have a separate table with the team names in, as early in the season a team may not have played both home or Away.Mark |
|
|
|
|
|
|
|