title: Union of multiple queries using the count function
body: <p>I'm working on learning more about how the <code>UNION</code> function works in SQL Server. </p>
  
  <p>I've got a query that is directed at a single table:</p>
  
  <pre><code>SELECT Category, COUNT(*) AS Number
  FROM Table1
  GROUP BY Category;
  </code></pre>
  
  <p>This returns the number of entries for each distinct line in the <code>Category</code> column.</p>
  
  <p>I have multiple tables that are organized by this <code>Category</code> column and I'd like to be able to have the results for every table returned by one query.</p>
  
  <p>It seems like <code>UNION</code> will accomplish what I want it to do but the way I've tried implementing the query doesn't work with <code>COUNT(*)</code>.</p>
  
  <pre><code>SELECT *
  FROM   (SELECT Table1.Category
                 Table1.COUNT(*) AS Number
          FROM   dbo.Table1
          UNION
          SELECT Table2.Category
                 Table2.COUNT(*) AS Number
          FROM   dbo.Table2) AS a
  GROUP  BY a.Category 
  </code></pre>
  
  <p>I'm sure there's an obvious reason why this doesn't work but can anyone point out what that is and how I could accomplish what I'm trying to do?</p>