SELECT		Z.Team
	
				
			
				,Sum(Games) as Pl
				,SUM(Win) as W
				,SUM(Draw) as D
				,SUM(Loss) as L
				,SUM(F) as F
				,SUM(A) as A
				,SUM(Total) as Tot
				,ROUND(AVG(GPG),2) AS GPG
				,SUM([Under 2.5]) AS [U 2.5]
				,SUM([Over 2.5]) AS [O 2.5]
				,SUM([BTS HT]) AS [BTS HT]
				,SUM([BTS FT]) AS [BTS FT]
				,SUM(Pts) AS [Pts]
				
	FROM		
		
			(		
			SELECT		Soccer_Base.dbo.Results.HomeTeam as Team
					  ,COUNT(Soccer_Base.dbo.Results.HomeTeam) as [Games]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Win]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Draw]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Loss]
		 	  
					  ,SUM(Soccer_Base.dbo.Results.HomeFT) as F
					  ,SUM(Soccer_Base.dbo.Results.AwayFT) as A
					  ,SUM(Soccer_Base.dbo.Results.HomeFT) + SUM(Soccer_Base.dbo.Results.AwayFT) AS Total
					  ,Round((SUM(Soccer_Base.dbo.Results.HomeFT) + SUM(Soccer_Base.dbo.Results.AwayFT))
					  / COUNT(Soccer_Base.dbo.Results.HomeTeam),2) AS GPG

					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT + Soccer_Base.dbo.Results.AwayFT<2.5 Then 1 ELSE 0 END) AS [Under 2.5]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT + Soccer_Base.dbo.Results.AwayFT>2.5 Then 1 ELSE 0 END) AS [Over 2.5]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeHT>0 AND Soccer_Base.dbo.Results.AwayHT>0 Then 1 ELSE 0 END) AS [BTS HT]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT>0 AND Soccer_Base.dbo.Results.AwayFT>0 Then 1 ELSE 0 END) AS [BTS FT]
					  
					  ,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 [Pts]
			  
			FROM		Soccer_Base.dbo.Results		LEFT JOIN Soccer_Base.dbo.Teams ON
													Soccer_Base.dbo.Teams.TeamName = Soccer_Base.dbo.Results.HomeTeam

			WHERE		Season = '2012-13' AND Competition = 'Premier League'
						

			GROUP BY	Soccer_Base.dbo.Results.HomeTeam
			
			
	UNION

			SELECT		Soccer_Base.dbo.Results.AwayTeam as Team
					  ,COUNT(Soccer_Base.dbo.Results.AwayTeam) as [Games]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Win]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Draw]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Loss]
		 	  
			  
					  ,SUM(Soccer_Base.dbo.Results.AwayFT) as F
					  ,SUM(Soccer_Base.dbo.Results.HomeFT) as A
					  ,SUM(Soccer_Base.dbo.Results.AwayFT) + SUM(Soccer_Base.dbo.Results.HomeFT) AS Total
					  ,Round((SUM(Soccer_Base.dbo.Results.AwayFT) + SUM(Soccer_Base.dbo.Results.HomeFT))
					  / COUNT(Soccer_Base.dbo.Results.AwayTeam),2) AS GPG
					  
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT + Soccer_Base.dbo.Results.AwayFT<2.5 Then 1 ELSE 0 END) AS [Under 2.5]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT + Soccer_Base.dbo.Results.AwayFT>2.5 Then 1 ELSE 0 END) AS [Over 2.5]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeHT>0 AND Soccer_Base.dbo.Results.AwayHT>0 Then 1 ELSE 0 END) AS [BTS HT]
					  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT>0 AND Soccer_Base.dbo.Results.AwayFT>0 Then 1 ELSE 0 END) AS [BTS FT]
					  
					  ,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 [Pts]
			  
			  
			FROM		Soccer_Base.dbo.Results			LEFT JOIN Soccer_Base.dbo.Teams ON
														Soccer_Base.dbo.Teams.TeamName = Soccer_Base.dbo.Results.AwayTeam

			WHERE		Season = '2012-13' AND Competition = 'Premier League'
						

			GROUP BY	Soccer_Base.dbo.Results.AwayTeam

			) z
	
	GROUP BY	Z.Team
	
	
	ORDER BY Pts desc

SELECT		Z.Team
	
				,RANK() OVER(ORDER BY SUM(Pts) DESC) as Position
				
				,SUM(Games) as Pl
				,SUM(Win) as W
				,SUM(Draw) as D
				,SUM(Loss) as L
				,SUM(F) as F
				,SUM(A) as A
				,SUM (F) - SUM(A) as GD
				,SUM(Total) as Tot
				,ROUND(AVG(GPG),2) AS GPG
				,SUM([Under 2.5]) AS [U 2.5]
				,SUM([Over 2.5]) AS [O 2.5]

....		,SUM([BTS FT]) AS [BTS FT]
		,SUM(Pts) AS [Pts]
		,RANK() OVER(ORDER BY SUM(Pts) DESC) AS Rnk	
FROM		
....