If your countrycodes are going to be embedded in the callednumber, you really need a delimiter like a dash "-". Then you can use some string functions to do the count. Ideally, as others suggested, the country code should be in its on field/column.
Below is the general idea for the SELECT, of course the problem in this data set is the country code "001": mysql> SELECT LEFT(callednumber, 4),COUNT(LEFT(callednumber, 4)) FROM call_log GROUP BY LEFT(callednumber, 4); +-----------------------+------------------------------+ | LEFT(callednumber, 4) | COUNT(LEFT(callednumber, 4)) | +-----------------------+------------------------------+ | 001X | 1 | | 0060 | 1 | | 0061 | 6 | | 0063 | 2 | +-----------------------+------------------------------+ The bottom line is that you really need to use cleaner more discrete data. Cheers....Cassj Jerry Schwartz wrote: > 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:[EMAIL PROTECTED] >> Sent: Friday, July 18, 2008 2:27 AM >> To: mysql@lists.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/[EMAIL PROTECTED] >> infoshop.com > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
