title: SQL: percentage deviation from average
body: <p>I have a table</p>
  
  <pre><code>student class score
  --------------------
  A        1       6
  B        1       5
  C        2       6
  </code></pre>
  
  <p>I would like to compute in SQL the score percentage deviation of each class from the average of scores in all classes; in other words</p>
  
  <pre><code>(avg_score_in_one_class- avg_score_all_classes)/avg_score_all_classes *100
  </code></pre>
  
  <p>I have computed it with SQL using this query</p>
  
  <pre><code>SELECT 
  class, 
  (AVG(score) - (SELECT AVG(score) FROM table))
  /
  (SELECT AVG(score) FROM table)
  *100
  FROM table
  GROUP BY class
  </code></pre>
  
  <p>The result should be</p>
  
  <pre><code>class deviation
  ---------------
  1      -2.9411764705882404
  2      5.882352941176465
  </code></pre>
  
  <p>Is there a way to write it better? For instance, I was thinking of the case in which you have many score columns.</p>
  
  <p>Following the suggestion by Gordon Linoff, I have found a partial solution:</p>
  
  <pre><code>WITH temp_table as 
  (
      SELECT
          class,
      AVG(score) OVER (PARTITION by class) as avg_class,
      AVG(score) OVER () as avg_score
      FROM table
  )
  
  SELECT
      class,
  AVG((avg_class-avg_score)/avg_score*100.0) as dev
  FROM temp_table
  GROUP BY class;
  </code></pre>
  
  <p>which is still not straightforward to be generalised to multiple score columns. </p>