mysql query trouble

Question

Hi ,

in my database i have phone numbers with country code , which look somthing like

0044-123456
0044-123456
0014-123456
0014-123456
0024-123456
0024-123456
0034-123456
0044-123456
0044-123456
0024-123456
0034-123456
084-123456
084-123456

i want to total up the numbers by country, something like this output

0044 (2)
0024 (2)
0034 (1)
084 (2)
064 (5)

Is it possible to do this with a SQL query?

Answer 1

Give this one a try:

SELECT count(SUBSTR(phoneNumber, 1, LOCATE("-", phoneNumber)))
FROM tableName
GROUP BY SUBSTR(phoneNumber, 1, LOCATE("-", phoneNumber));

Answer 2

This should do the trick:

  SELECT phoneNumber,
         SUBSTR(phoneNumber, 1, LOCATE("-", phoneNumber) - 1) AS countryCode,
         COUNT(*) AS count
    FROM phoneNumbers
GROUP BY countryCode

i.e. extract the country code from the number, and group on it.