title: Multiple Select queries in MySQL
body: <p>I have a table as such.</p>
  
  <pre><code>home team | away team | attendance | home or away|
         SD |        WP |    SD OVAL |        4000 | H |
         WP |        SD |    SD OVAL |        4000 | A |
  </code></pre>
  
  <p>I want to be able to generate one table to display as such</p>
  
  <pre><code>Team  | Home Total  | Home Average | Away Total | Away Average | Total | Average |
    SD  |       4000  |         4000 |          0 |           0  |  4000 |    4000 |
    WP  |          0  |            0 |       4000 |        4000  |  4000 |    4000 |
  </code></pre>
  
  <p>I have tried joins and union, although UNION gives me the best result, although the results are returned in only three columns</p>
  
  <pre><code>Team, HomeAttendance, AveHomeAttendance
  </code></pre>
  
  <p>This is my query (I am still new in MYSQL)</p>
  
  <pre><code>SELECT Team,
         SUM(Attendance) AS HomeAttendance,
         ROUND(AVG(Attendance),0) AS AveHomeAttendance
  FROM MatchDetails
  WHERE Season = 2014
    AND HA = 'H'
  GROUP BY TEAM
  UNION
  SELECT Team,
         SUM(Attendance) AS AwayAttendance,
         ROUND(AVG(Attendance),0) AS AveAwayAttendance
  FROM MatchDetails
  WHERE Season = 2014
    AND HA = 'A'
  GROUP BY TEAM
  UNION
  SELECT Team,
         SUM(Attendance) AS TotalAttendance,
         ROUND(AVG(Attendance),0) AS AveTotalAttendance
  FROM MatchDetails
  WHERE Season = 2014
  GROUP BY TEAM
  </code></pre>