Union of two queries? - SQL Server Forums

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

Union of two queries?

Author

Topic

mosiki
Starting Member

12 Posts

Posted - 2013-07-09 : 10:18:52

Any ideas why this code wont work? Attempting to join Home Games and Away games data from two queries.

SELECT		Soccer_Base.dbo.Teams.TeamName
				,Sum(Games) as games
				
	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]
		 	  

			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]
		 	  

			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	Soccer_Base.dbo.Teams.TeamName