MySQL group by with where clause with having count greater than 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.

From SUBSTR doc:

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

So try this:

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