From: Jerry Schwartz (jschwartzthe-infoshop.com)
Date: Fri Jul 18 2008 - 09:04:05 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

How are you storing phone numbers? I don't think there are leading zeroes in
country codes, and country codes are not all the same length. Are you
padding them with leading zeroes to five characters?

If you have padded them so that the first five characters are always the
country code, then you can simply do

SELECT

>-----Original Message-----
>From: Ron [mailto:ronsilverbackasp.com]
>Sent: Friday, July 18, 2008 2:27 AM
>To: mysqllists.mysql.com
>Subject: counting by countrycode
>
>------------------------------------------------------------------------
>-----------------------------------------------------------------
>| customercode | customer name | customerphonenumber | callednumber
> | calldate | callstart | callend | callduration | callcost |
>------------------------------------------------------------------------
>-----------------------------------------------------------------
>| 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX
> | 2008-07-16 | 15:25:19 | 15:26:00 | 1 | 0.229375 |
>| 10000116 | Company Name | Customer NUmber | 0063XXXXXXXXX
> | 2008-07-16 | 15:25:21 | 15:26:44 | 2 | 0.07759 |
>| 10000116 | Company Name | Customer NUmber | 001XXXXXXXXXX
> | 2008-07-16 | 15:25:24 | 15:25:34 | 1 | 0.229375 |
>| 10000116 | Company Name | Customer NUmber | 0060XXXXXXXXX
> | 2008-07-16 | 15:25:25 | 15:29:33 | 5 | 1.14688 |
>| 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX
> | 2008-07-16 | 15:25:29 | 15:25:58 | 1 | 0.038795 |
>| 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX
> | 2008-07-16 | 15:25:29 | 15:25:48 | 1 | 0.038795 |
>| 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX
> | 2008-07-16 | 15:25:32 | 15:25:54 | 1 | 0.038795 |
>| 10000116 | Company Name | Customer NUmber | 0063XXXXXXXXX
> | 2008-07-16 | 15:25:33 | 15:25:45 | 1 | 0.038795 |
>| 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX
> | 2008-07-16 | 15:25:35 | 15:26:29 | 1 | 0.229375 |
>| 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX
> | 2008-07-16 | 15:25:38 | 15:25:53 | 1 | 0.038795 |
>------------------------------------------------------------------------
>------------------------------------------------------------------
>
>Hi,
>
>How can i count by countrycode with that table? I kind of need a summary
> to count how many calls went to certain country. Thank You. I hope my
>question is clear.
>
[JS] How are you storing phone numbers? I don't think there are leading
zeroes in country codes, and country codes are not all the same length. Are
you padding them with leading zeroes to five characters?

If you have padded them so that the first five characters are always the
country code, then you can simply do

SELECT SUBSTRING(callednumber,1,5) AS countrycode FROM tablename GROUP BY
countrycode;

>Regards
>Ron
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartzthe-
>infoshop.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]