title: Trying to count an aggregate result in Sql Server 2008 R2
body: <p>I have produced a query that lists out some records from my source data (at the bottom of this)</p>
  
  <p>My table setup is made up of 3 tables that look like </p>
  
  <p><strong>tu_unit_tbl</strong></p>
  
  <pre><code>| unit_id | unit_name | unit_subject | 
  </code></pre>
  
  <p><strong>sp_student_tbl</strong></p>
  
  <pre><code>| st_id |  st_fname  | st_lname |
  </code></pre>
  
  <p><strong>sp_test_tbl</strong></p>
  
  <pre><code>| ts_id |  ts_st_id  |  ts_unit_id | ts_session | 
  </code></pre>
  
  <p>My query so far, <strong>which works fine so far</strong>, but I need to add a computed field onto it:</p>
  
  <pre><code>SELECT  U.unit_subject,
          U.unit_name,
          S.st_fname + ' ' + S.st_lname as sname,
          T.ts_st_id,
          T.ts_session
  FROM sp_test_tbl T
  JOIN sp_student_tbl S on T.ts_st_id = st_id
  JOIN tu_unit_tbl U on unit_id = ts_unit_id
  
  WHERE ts_st_id = 184318
  GROUP BY unit_name, U.unit_subject, ts_session,  st_lname, st_fname, ts_st_id
  </code></pre>
  
  <p>It produces this:</p>
  
  <p>if you notice, there are 3 rows.  The first two have the same "unit name".  I am looking to add a field to count the occurrences of these rows</p>
  
  <pre><code>unit_subject | unit_name                            | sname    | ts_st_id | ts_session
  --------------------------------------
  Mathematics  | Algebra, Patterns, and Relationships | Frog Man | 184318   | 2012-07-31 15:22:42.000
  Mathematics  | Algebra, Patterns, and Relationships | Frog Man | 184318   | 2012-07-31 15:25:47.000
  Mathematics  | Data, Statistics, and Probability    | Frog Man | 184318   | 2012-07-31 15:25:59.000
  </code></pre>
  
  <p><strong>What I am trying to get</strong></p>
  
  <pre><code>unit_subject | unit_name                            | sname    | **COUNT OF UNIT_NAME**
  ---------------------------------------------------------------------------------------
  Mathematics  | Algebra, Patterns, and Relationships | Frog Man | **2**
  Mathematics  | Data, Statistics, and Probability    | Frog Man | **1**
  </code></pre>
  
  <p>Note that there are only 2 records now, because the first 2 were combined and counted..</p>
  
  <p>This is an aggregate result, of course, and I want to count those aggregate items in a new field (called Count of Unit Name)</p>
  
  <p>my attempts to put in a Count(*) field, or even <code>JOIN</code>ing to a subquery that attempts to count the fields does not give me a count of the aggregate, but rather, counts each item that I aggregated!</p>
  
  <p>How can I count the repeating <strong>unit names</strong> in my result?</p>
  
  <p>Here’s the Raw data: (the repeating data indicates a test session for a student. each record is part of a session)</p>
  
  <pre><code>select ts_st_id,
         ts_session
  from sp_test_tbl
  where ts_st_id = 184318
  </code></pre>
  
  <blockquote>
    <p>ts_st_id  | ts_session </p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000</p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000</p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000</p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000</p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000</p>
    
    <p>184318    2012-07-31 15:22:42.000 </p>
    
    <p>184318    2012-07-31 15:22:42.000</p>
    
    <p>184318    2012-07-31 15:25:47.000 </p>
    
    <p>184318    2012-07-31 15:25:47.000</p>
    
    <p>184318    2012-07-31 15:25:47.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
    
    <p>184318    2012-07-31 15:25:59.000 </p>
    
    <p>184318    2012-07-31 15:25:59.000</p>
  </blockquote>
  
  <p>My latest attempt (recommended by praveen) seems like it might work, but gives an error.</p>
  
  <pre><code>WITH CTE (unit_subject,
          unitName,
          sname,
          ts_st_id,
          ts_session)
  AS
  (
      SELECT  U.unit_subject,
          U.unit_name as unitName,
          S.st_fname + ' ' + S.st_lname as sname,
          T.ts_st_id,
          T.ts_session
      FROM sp_test_tbl T
      JOIN sp_student_tbl S on T.ts_st_id = st_id
      JOIN tu_unit_tbl U on unit_id = ts_unit_id
  
      WHERE ts_st_id = 1234
      GROUP BY unit_name, U.unit_subject, ts_session,  st_lname, st_fname, ts_st_id
  )
  
  select count(unitName) over (partition by unit_name)
  
  Go
  </code></pre>
  
  <p>Generates the following Error Message:</p>
  
  <pre><code>Msg 207, Level 16, State 1, Line 22
  Invalid column name 'unit_name'.
  Msg 207, Level 16, State 1, Line 22
  Invalid column name 'unitName'.
  </code></pre>