title: SQL AVG(COUNT(*))?
body: <p>I'm trying to find out the average number of times a value appears in a column, group it based on another column and then perform a calculation on it.</p>
  
  <p>I have 3 tables a little like this  </p>
  
  <pre><code>DVD
  
  ID | NAME
  1  | 1       
  2  | 1     
  3  | 2      
  4  | 3
  
  COPY 
  
  ID | DVDID   
  1  | 1  
  2  | 1  
  3  | 2  
  4  | 3  
  5  | 1
  
  LOAN
  
  ID | DVDID | COPYID  
  1  | 1     |  1  
  2  | 1     |  2  
  3  | 2     |  3    
  4  | 3     |  4  
  5  | 1     |  5
  6  | 1     |  5
  7  | 1     |  5
  8  | 1     |  2
  </code></pre>
  
  <p>etc</p>
  
  <p>Basically, I'm trying to find all the copy ids that appear in the loan table LESS times than the average number of times for all copies of that DVD.  </p>
  
  <p>So in the example above, copy 5 of dvd 1 appears 3 times, copy 2 twice and copy 1 once so the average for that DVD is 2.  I want to list all the copies of that (and each other) dvd that appear less than that number in the Loan table.</p>
  
  <p>I hope that makes a bit more sense...</p>
  
  <p>Thanks</p>