Have Geek Brain

Mysql query error

in my database i have country code phone numbers that 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 sum 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?

+2


a source to share


2 answers


Try:



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


a source


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 it.

+1


a source






More articles:


All Articles