title: MySQL query issue. SUM TOP 3 per type and top 1 per group by reference
body: <p>I have been trying to write an SQL query to select the top 3 per type per user and sum together with the top 1 of another type per user. I apologise if it sounds complex but I can't think of a better way to describe it. The query must be compatible with MySQL 5.x.</p>
  
  <p>I have been trying to adapt a previous query with no joy. It has currently been setup to return the first 5 rows per type per user and treat every other row as a value of 1. I've tried to change some clauses to produce the desired effect with no joy. </p>
  
  <pre><code>SELECT
   wsf_ref
  ,sum(wsf_value) as total
  from (select
  *,
  ( select count(*)
   from individual u
   where
     t.wsf_ref = u.wsf_ref and
     t.type = u.type and
     t.wsf_value &lt;= u.wsf_value
  ) as number
  from individual t) t
  where (number &lt;= 3 and type &lt;&gt; 'blue' and status = 'Approved' and wsf_progress IN ('Day 1', 'Day 2', 'Day 3'))
  or (number = 1 and type = 'blue' and status = 'Approved' and wsf_progress IN ('Day 1', 'Day 2', 'Day 3'))
  group by wsf_ref
  order by total desc  
  </code></pre>
  
  <h2>Example Data</h2>
  
  <pre><code>id     wsf_ref   status     type       wsf_progress   wsf_value
  1      001       Approved       orange     Day 1          5
  2      001       Approved       orange     Day 1          10  *
  3      001       Approved       orange     Day 1          20  *
  4      001       Approved       orange     Day 1          10  *
  5      001       Approved       blue       Day 1          10 
  6      001       Approved       blue       Day 1          25  * 
  7      002       Approved       red        Day 1          10
  8      002       Approved       red        Day 1          20  *
  9      002       Approved       red        Day 1          30  *
  10     002       Approved       red        Day 1          20  *
  11     002       Approved       orange     Day 1          10
  12     002       Approved       orange     Day 1          20  *
  13     002       Approved       orange     Day 1          15  *
  14     002       Approved       orange     Day 1          40  *
  15     002       Approved       blue       Day 1          20
  16     002       Approved       blue       Day 1          35
  17     002       Approved       blue       Day 1          50  *
  
  * denotes rows to be summed in the example.
  </code></pre>
  
  <p>For this example the type of 'blue' is the TOP 1 only. The 'status and 'progress' are not really relevant in this example but I would like them included as WHERE criteria.</p>
  
  <pre><code>Expected Results
  ----------------------
  wsf_ref     total
  002         195
  001         65
  
  Current Results
  ----------------------
  wsf_ref     total
  002         120
  001         45
  </code></pre>
  
  <p>It looks like it's only selecting the top 1 for each type and ignoring the number &lt;= 3 clause.</p>