Combining 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

Combining two queries

Author

Topic

mosiki
Starting Member

12 Posts

Posted - 2013-07-19 : 06:19:18

Hi

I have the below query. It pulls the last '@X' home games for the selected '@Team' (based on the date).

SELECT		TOP (@X) *

FROM		Soccer_Base.dbo.Results

WHERE		Soccer_Base.dbo.Results.HomeTeam = @Team

ORDER BY	Soccer_Base.dbo.Results.Date desc

I aslo have another query which sums all the home games for all teams for the selected '@Season'.

SELECT		Soccer_Base.dbo.Results.HomeTeam as Team
			  ,COUNT(Soccer_Base.dbo.Results.HomeTeam) as [Home Games]
	/*Win*/	  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Win]
	/*Draw*/  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Draw]
	/*Loss*/  ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Loss]

	/*AGF*/			,ROUND(SUM(Soccer_Base.dbo.Results.HomeFT)/COUNT(Soccer_Base.dbo.Results.HomeTeam),2) as AGF
	/*For*/			,SUM(Soccer_Base.dbo.Results.HomeFT) as F
	/*Against*/		,SUM(Soccer_Base.dbo.Results.AwayFT) as A
	/*AGA*/			,ROUND(SUM(Soccer_Base.dbo.Results.AwayFT)/COUNT(Soccer_Base.dbo.Results.HomeTeam),2) as AGA
	
	/*Total*/		,SUM(Soccer_Base.dbo.Results.HomeFT) + SUM(Soccer_Base.dbo.Results.AwayFT) AS Total
	/*GPG*/			,Round((SUM(Soccer_Base.dbo.Results.HomeFT) + SUM(Soccer_Base.dbo.Results.AwayFT))
					/ COUNT(Soccer_Base.dbo.Results.HomeTeam),2) AS GPG
  
			  
	/* Home Pts */  ,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]
			  
			  

	FROM		Soccer_Base.dbo.Results

	WHERE		Season = @Season
				
				

	GROUP BY	Soccer_Base.dbo.Results.HomeTeam
	
	


ORDER BY	HomeTeam

I am trying to write a query that will sum the last four home games for all teams. Any ideas? Some sort of sub query maybe?

Many Thanks

Mosiki.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-19 : 06:23:57

[code]
SELECT Soccer_Base.dbo.Results.HomeTeam as Team
,COUNT(Soccer_Base.dbo.Results.HomeTeam) as [Home Games]
/*Win*/ ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Win]
/*Draw*/ ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Draw]
/*Loss*/ ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Home Loss]

/*AGF*/ ,ROUND(SUM(Soccer_Base.dbo.Results.HomeFT)/COUNT(Soccer_Base.dbo.Results.HomeTeam),2) as AGF
/*For*/ ,SUM(Soccer_Base.dbo.Results.HomeFT) as F
/*Against*/ ,SUM(Soccer_Base.dbo.Results.AwayFT) as A
/*AGA*/ ,ROUND(SUM(Soccer_Base.dbo.Results.AwayFT)/COUNT(Soccer_Base.dbo.Results.HomeTeam),2) as AGA

/*Total*/ ,SUM(Soccer_Base.dbo.Results.HomeFT) + SUM(Soccer_Base.dbo.Results.AwayFT) AS Total
/*GPG*/ ,Round((SUM(Soccer_Base.dbo.Results.HomeFT) + SUM(Soccer_Base.dbo.Results.AwayFT))
/ COUNT(Soccer_Base.dbo.Results.HomeTeam),2) AS GPG

/* Home Pts */ ,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]

FROM (SELECT TOP (@X) *

FROM Soccer_Base.dbo.Results

WHERE Soccer_Base.dbo.Results.HomeTeam = @Team

ORDER BY Soccer_Base.dbo.Results.Date desc
)t
WHERE Season = @Season

GROUP BY Soccer_Base.dbo.Results.HomeTeam

ORDER BY HomeTeam

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources