Mysql COUNT, GROUP BY and ORDER BY

This sounds quite simple but I just can't figure it out.

I have a table orders (id, username, telephone_number).

I want to get number of orders from one u开发者_运维知识库ser by comparing the last 8 numbers in telephone_number.

I tried using SUBSTR(telephone_number, -8), I've searched and experimented a lot, but still I can't get it to work.

Any suggestions?

Untested:

SELECT
    COUNT(*) AS cnt,
    *
FROM
    Orders
GROUP BY
    SUBSTR(telephone_number, -8)
ORDER BY
    cnt DESC

The idea:

1. Select COUNT(*) (i.e., number of rows in each GROUPing) and all fields from Orders (*)
2. GROUP by the last eight digits of telephone_number¹
3. Optionally, ORDER by number of rows in GROUPing descending.

¹⁾ If you plan to do this type of query often, some kind of index on the last part of the phone number could be desirable. How this could be best implemented depends on the concrete values stored in the field.

//Memory intensive.

SELECT COUNT(*) FROM `orders` WHERE REGEXP `telephone_number` = '(.*?)12345678'

OR

//The same, but better and quicker.

SELECT COUNT(*) FROM `orders` WHERE `telephone_number` LIKE '%12345678'

You can use the below query to get last 8 characters from a column values.

select right(rtrim(First_Name),8) FROM [ated].[dbo].[Employee]