MySQL group by with where clause with having count greater than 1

Question

This should be really simple I know, but for the life of me I can't get my query to do what I need.

I am looking to find all rows that are a certain status (paid) grouped by ref but only if there is more than 1 row found.

This is my sample table:

+-----+----------+----------+-------+
| 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 |
+-----+----------+----------+-------+

This is my SQL:

SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,0,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC

I need to have it matching by SUBSTR due to ref sometimes containing appended numbers.

The problem is that my query is returning this:

+-----+----------+---------+-------+
| id  | deleted  | status  |  ref  |
+-----+----------+---------+-------+
|   2 |        0 | paid    | 10001 |
+-----+----------+---------+-------+

When I'd like it to be returning refs 10001, 10003 & 10006.

Can anyone help me work out what I am doing wrong?

Thanks

Answer 1

Try

SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,1,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC

The position-argument for SUBSTR starts with 1, not with 0.