title: Frequency distribution by column in SQL Server
body: <p>I have a table with the following structure</p>
  
  <pre><code>  year    age   score    weight
    ------------------------------
    2008    16    100      3
    2008    25    150      2
    2009    40    210      2
    2009    22    50       3
    2009    65    90       3
  </code></pre>
  
  <p>I need to find the frequency distribution of score by age.</p>
  
  <p>Expected output is the distribution of score by age in %:
  (Not accurate output - for depiction only. the row totals 1)</p>
  
  <pre><code>                   0-50  51-100  101-150  151-200  201-250
   2008   16-35      0.3    0.2      0.1     0.4      0
   2008   36-40 
   2008   41-65
   2008   66+
   2008   All
   2009   16-35
   2009   36-40
   2009   41-65
   2009   66+
  </code></pre>
  
  <p>I came up with the below query say for one group</p>
  
  <pre><code>select 
      yearofsale,
      sum(sum(case 
                 when age between 16 and 35 AND score between 0 and 50 
                    then 1 
                 else 0 
              end) * weight) / sum(case 
                                      when age between 16 and 35 and score between 0 and 50 
                                         then weight 
                                      else 1 end)
  from 
      table
  group by 
      yearofsale
  </code></pre>
  
  <p>but I'm pretty sure there is an easier way to do this. Any idea?</p>
  
  <p>Thanks,
  bee</p>