title: MySQL group by with where clause with having count greater than 1
body: <p>This should be really simple I know, but for the life of me I can't get my query to do what I need.</p>
  
  <p>I am looking to find all rows that are a certain status (<code>paid</code>) grouped by <code>ref</code> but only if there is more than 1 row found.</p>
  
  <p>This is my sample table:</p>
  
  <pre><code>+-----+----------+----------+-------+
  | id  | deleted  | status   |  ref  |
  +-----+----------+----------+-------+
  |   1 |        0 | pending  | 10001 |
  |   2 |        0 | paid     | 10001 |
  |   3 |        0 | paid     | 10001 |
  |   4 |        0 | paid     | 10002 |
  |   5 |        1 | pending  | 10002 |
  |   6 |        1 | paid     | 10002 |
  |   7 |        0 | pending  | 10003 |
  |   8 |        0 | paid     | 10003 |
  |   9 |        0 | paid     | 10003 |
  |  10 |        0 | paid     | 10003 |
  |  11 |        0 | pending  | 10004 |
  |  12 |        0 | paid     | 10004 |
  |  13 |        1 | pending  | 10005 |
  |  14 |        1 | paid     | 10005 |
  |  15 |        1 | paid     | 10005 |
  |  16 |        0 | paid     | 10005 |
  |  17 |        0 | pending  | 10006 |
  |  18 |        0 | paid     | 10006 |
  |  19 |        0 | paid     | 10006 |
  +-----+----------+----------+-------+
  </code></pre>
  
  <p>This is my SQL:</p>
  
  <pre><code>SELECT * FROM `orders`
  WHERE `deleted` = 0 AND `status` = 'paid'
  GROUP BY SUBSTR(`ref`,0,5)
  HAVING COUNT(*) &gt; 1
  ORDER BY `id` DESC
  </code></pre>
  
  <p>I need to have it matching by SUBSTR due to <code>ref</code> sometimes containing appended numbers.</p>
  
  <p>The problem is that my query is returning this:</p>
  
  <pre><code>+-----+----------+---------+-------+
  | id  | deleted  | status  |  ref  |
  +-----+----------+---------+-------+
  |   2 |        0 | paid    | 10001 |
  +-----+----------+---------+-------+
  </code></pre>
  
  <p>When I'd like it to be returning <code>ref</code>s <code>10001</code>, <code>10003</code> &amp; <code>10006</code>.</p>
  
  <p>Can anyone help me work out what I am doing wrong?</p>
  
  <p>Thanks</p>