title: MySQL Query - Join/Union?
body: <p>I have two queries which basically groups the home team for a selected month to get their statistics and then the other groups the away team for the selected month to get their statistics but the third query I want an overall set of statistics so the two of the combined but I am running into a brick wall with everything I try!</p>
  
  <pre><code>SELECT HomeTeam,
  SUM(CASE WHEN Month = $FixSelMonth THEN 1 ELSE 0 END) AS Played,
  SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Home' THEN 1 ELSE 0 END) Won,
  SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Draw' THEN 1 ELSE 0 END) Draw,
  SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Away' THEN 1 ELSE 0 END) Lost,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Home' THEN 1 ELSE 0 END) / 
  SUM(CASE WHEN Month = $FixSelMonth THEN 1 ELSE 0 END),2) AS StrikeRate,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr &lt;&gt; 'TBP' THEN lsph ELSE 0 END),2) AS WinLSP,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr &lt;&gt; 'TBP' THEN lspd ELSE 0 END),2) AS DrawLSP,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr &lt;&gt; 'TBP' THEN lspa ELSE 0 END),2) AS LoseLSP
  FROM Results_Football
  GROUP BY HomeTeam
  HAVING Played &gt;= 20 AND WinLSP &gt; 0
  ORDER BY StrikeRate DESC
  LIMIT 10
  
  SELECT AwayTeam,
  SUM(CASE WHEN Month = $FixSelMonth THEN 1 ELSE 0 END) AS Played,
  SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Away' THEN 1 ELSE 0 END) Won,
  SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Draw' THEN 1 ELSE 0 END) Draw,
  SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Home' THEN 1 ELSE 0 END) Lost,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr = 'Home' THEN 1 ELSE 0 END) / 
  SUM(CASE WHEN Month = $FixSelMonth THEN 1 ELSE 0 END),2) AS StrikeRate,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr &lt;&gt; 'TBP' THEN lspa ELSE 0 END),2) AS WinLSP,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr &lt;&gt; 'TBP' THEN lspd ELSE 0 END),2) AS DrawLSP,
  ROUND(SUM(CASE WHEN Month = $FixSelMonth AND ftr &lt;&gt; 'TBP' THEN lsph ELSE 0 END),2) AS LoseLSP
  FROM Results_Football
  GROUP BY AwayTeam
  HAVING Played &gt;= 20 AND WinLSP &gt; 0
  ORDER BY StrikeRate DESC
  LIMIT 10
  </code></pre>
  
  <p>So basically what I am trying to do is group by the teams and also get their statistics, if there a way of combining the two queries to achieve this?</p>
  
  <p>Thank you in advance!</p>