SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Duplicated column data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mosiki
Starting Member

12 Posts

Posted - 06/24/2013 :  11:01:57  Show Profile  Reply with Quote
Hi

When 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 Pts
Man United 19 16 0 3 48 19 3 0 16 48
Man City 19 14 3 2 45 19 2 3 14 45
Everton 19 12 6 1 42 19 1 6 12 42
Chelsea 19 12 5 2 41 19 2 5 12 41
Arsenal 19 11 5 3 38 19 3 5 11 38
Tottenham 19 11 5 3 38 19 3 5 11 38
West Ham 19 9 6 4 33 19 4 6 9 33
Liverpool 19 9 6 4 33 19 4 6 9 33
Norwich 19 8 7 4 31 19 4 7 8 31
West Brom 19 9 4 6 31 19 6 4 9 31
Stoke 19 7 7 5 28 19 5 7 7 28
Newcastle 19 9 1 9 28 19 9 1 9 28
Swansea 19 6 8 5 26 19 5 8 6 26
Southampton 19 6 7 6 25 19 6 7 6 25
Fulham 19 7 3 9 24 19 9 3 7 24
Sunderland 19 5 8 6 23 19 6 8 5 23
Reading 19 4 8 7 20 19 7 8 4 20
Aston Villa 19 5 5 9 20 19 9 5 5 20
Wigan 19 4 6 9 18 19 9 6 4 18
QPR 19 2 8 9 14 19 9 8 2 14




The 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?

Thanks

Mosiki.

mhorseman
Starting Member

United Kingdom
16 Posts

Posted - 06/24/2013 :  12:23:08  Show Profile  Reply with Quote
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 #team
create table #team(
team char(10)
)

insert into #team
select 'Arsenal' union
select 'Bradford' union
select 'Chelsea' union
select 'Derby'

drop table #res
create table #res(
hometeam char(10),
awayteam char(10),
homeft int,
awayft int
)

insert into #res
select 'Arsenal','Bradford',3,1 union
select 'Arsenal','Chelsea',0,0 union
select 'Arsenal','Derby',1,2 union
select 'Bradford','Chelsea',2,2


select 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 when
homeft > awayft then 1 end),0) HW,
isnull(sum(case when
homeft = awayft then 1 end),0) HD,
isnull(sum(case when
homeft < awayft then 1 end),0) HL,
0 aw,0 ad,0 al
from #team t left join #res r on t.team = r.hometeam
group by t.team

union

select t.team,count(awayteam) P,
0 hw,0 hd,0 hl,
isnull(sum(case when
homeft < awayft then 1 end),0) AW,
isnull(sum(case when
homeft = awayft then 1 end),0) AD,
isnull(sum(case when
homeft > awayft then 1 end),0) AL
from #team t left join #res r on t.team = r.awayteam
group by t.team
) z

group by team

You 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000