title: Count aggregate does not work using PIVOT in MSSQL Server?
body: <p>I am using a pivot to generate a cross-tabulation report to summarize data using MSSQL Server.  However, when I run my query, it didn't produce the result which I expect to.</p>
  
  <p>This is the sql pivot query:</p>
  
  <pre><code>    select Station, 
         [1] as Good,
         [3] as Bad,
         [5] as Deactivated,
         [6] as Deleted
         --StateCount
  from
  (
  select m.MetaData1 as Station,
         t.ID,
         --m.StateID,
         count(t.[State]) as StateCount
  from MasterTags m inner join TagStates t on t.ID = m.StateID
  where (m.MetaData1 is not null and m.MetaData1 != '') and (m.PIServerID = 1)
  group by m.MetaData1, t.ID, m.StateID, t.[State]
  ) as result
  PIVOT (count(result.StateCount) for ID in ([1], [3], [5], [6])) pvt
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Station | Good  | Bad  | Deactivated | Deleted | StateCount
  ------- +-------+------+-------------+---------+-----------
  ABY     |   0   |  0   |     0       |   1     |     4
  ABY     |   0   |  1   |     0       |   0     |    18
  ABY     |   1   |  0   |     0       |   0     |    40
  FTB     |   0   |  1   |     0       |   0     |    10
  FTB     |   1   |  0   |     0       |   0     |   121
  KIK     |   0   |  1   |     0       |   0     |     1
  KIK     |   1   |  0   |     0       |   0     |    45 
  </code></pre>
  
  <p>I have included the <code>StateCount</code> column above to show the actual <code>count(t.[State])</code> values.  But, instead I got a value of <code>1</code> for each of these columns (Good, Bad, Deactivated, Deleted) in the final result set. I would expect the <code>StateCount</code> values will be the data for these columns (as shown below).</p>
  
  <p>Expected Output:</p>
  
  <pre><code>Station | Good  | Bad  | Deactivated | Deleted
  ------- +-------+------+-------------+--------
  ABY     |   40  |  18  |     0       |   4
  FTB     |  121  |  10  |     0       |   0
  KIK     |   45  |   1  |     0       |   0
  </code></pre>
  
  <p>This is my first time to use a pivot relation operator in a table-valued expression.  Perhaps I don't really understand how to use it correctly.  Is my pivot query wrong?</p>
  
  <p>Any help is greatly appreciated.</p>