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
 General SQL Server Forums
 New to SQL Server Programming
 Duplicated column data

Author  Topic 

mosiki
Starting Member

12 Posts

Posted - 2013-06-24 : 11:01:57
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

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 #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
   

- Advertisement -