Search code examples
mysqlcountgroup-bysql-order-bysubstr

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 user 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?

Solution

  • 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_number1
    3. Optionally, ORDER by number of rows in GROUPing descending.

    1) 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.