title: T-SQL Multi-layered CTE query with Aggregates
body: <p>I have a long Common Table Expression (CTE) query which is trying to calculate percent difference between each users' average score and group average score.</p>
  
  <p>I would like for my multi-layered CTE query to filter and reduce bulk of records down to the following table:</p>
  
  <pre><code>UserID     Tag     UserAvg     GroupAvg     PercentDifference
  1          Cat     72.50       73           -0.68
  2          Cat     75.50       73           3.36
  3          Cat     75          73           2.70
  4          Cat     73.25       73           0.34
  5          Cat     52.3333     73           -32.97
  6          Cat     86.25       73           16.64
  </code></pre>
  
  <p>My problem is getting <code>GroupAvg</code> column so that I can perform <code>% Difference</code> calculation.</p>
  
  <p>To illustrate the current approach I am using; here is the summary of my CTE query:</p>
  
  <pre><code>WITH
  -- select 1st 3 columns
  UserScores    AS (select UserID, Tag, Score FROM {multiple-table} WHERE Tag = 'Cat'),
  -- add UserAvg column by grouping records
  ScoreAverages AS (select UserID, Tag, AVG(Score) AS UserAvg GROUP BY UserID, Tag FROM UserScores),
  -- calculate GroupAvg
  GroupAverage  AS (select AVG(UserAvg) AS GroupAvg FROM ScoreAverages),
  -- calculate % difference
  PercentDiff   AS (select UserID, Tag, UserAvg, 73 AS GroupAvg, (((UserAvg-73)/((UserAvg+73)/2))*100) AS PercentDifference FROM ScoreAverages )
  -- do something with results
  select * from PercentDiff
  </code></pre>
  
  <p>Simple enough; right?</p>
  
  <p>Notice that I have hard coded <code>73</code> as my <code>GroupAvg</code> value. I am unsure how to construct required sql query that would allow me to go from <code>ScoreAverages</code> to <code>PercentDiff</code> table.</p>
  
  <p>Is it possible to perform SELECT within a SELECT statement? And I am not looking for something of the following:</p>
  
  <pre><code>select * from X where Id in (select Id from Y where Name like '%abc%')
  </code></pre>
  
  <p>Or I am simply trying to do too much in one go?</p>