title: Sum a union query
body: <p>I'm using Microsoft SQL Svr Mgmt Studio 2008.  I don't have access to create a temporary table (company restricts ability to create or modify tables) or I would use that to solve this problem.  </p>
  
  <p>I have successfully used a union query to combine the results of three select queries.  Now I am trying to sum the results of the union.  </p>
  
  <p>When I execute the query below I receive:</p>
  
  <pre><code>Incorrect syntax near the keyword 'GROUP'
  </code></pre>
  
  <p>And then when I remove the group by I get:</p>
  
  <pre><code>Incorrect syntax near ')'
  </code></pre>
  
  <p>Here's my query so far:</p>
  
  <pre><code>Select Period, PCC, SUM(BasicHits), SUM(FareHits), SUM(SearchHits)
  From (
  
  SELECT     AAAPeriod AS Period, 
  AAAFromPCC AS PCC, 
  - SUM(AAABasic) AS BasicHits,
  - SUM(AAAFare) AS FareHits,
  - SUM(AAASearch) AS SearchHits
  FROM        HitsAaa
  HAVING     (AAAPeriod = N'2010-10') 
  
  UNION ALL
  
  SELECT     AAAPeriod, 
  AAAtoPCC,
  SUM(AAABasic),
  SUM(AAAFare),
  SUM(AAASearch) 
  FROM        HitsAaa
  HAVING     (AAAPeriod = N'2010-10')
  
  UNION ALL
  
  SELECT      AgtPeriod, 
  AgtPcc, 
  SUM(AgtBasic),
  SUM(AgtFare),
  SUM(AgtSearch)
  FROM          HitsAgent
  HAVING      (AgtPeriod = N'2010-10')
  
  )GROUP BY Period, PCC
  </code></pre>
  
  <p>I haven't been able to find a solution to this on any of the previous questions.</p>