a quick question for the mysql or php gurus.
I have a table with a field for the telephone number. this includes the npa, nnx and the last 4 digits in the following order. 415 555 1234
I have about 400 numbers in the field and would like to identify the quantity using count() (and of course GROUP) of the quantity of each area code. thus 201(1), 415(20), 800(47) etc.
But I do not know how to call in wild cards to do the read of just the first 3 digits.
The format is uniform, there are no 011's.
Here is more or less where I am just now.
SELECT phone FROM mytable ORDER BY phone asc;
If I move the areacode to a seperate col. the problem is automatically solved. And I can do that one.
But is there a way without adding a col.
I don't see why you can't use LEFT() for this task. This untested but should work
SELECT LEFT(phone, 3) as areacode, count(phone) as count FROM mytable group by phone
where phone like '123%'
will return all phone numbers starting with 123.
Now if you want to count them up and all of that you can do some grouping and looping I'm assuming you know how to do that?
Thanks freddy. I need to just sit back and read the list of features. But, it sounds really good.
Thanks Chris. Your answer fits well for another question that I had not thought of until you answered it.
Kudos and Karma all round.
Tried freddy's idea and just a tad of modification got the following. And it does exactly what I think I need.
SELECT LEFT(phone, 3) as areacode, count(phone) from mytable group by areacode.
And the beauty is that the areacode exists seperately only temporarily in an alias as a seperate item.
Thanks again to all.
I would use seperate columns for country code and area code. IMHO
Your suggestion is a good one. But I just want to handle the north american plan now. The addition of a column and a where statement in the code should do the trick.
Thanks a lot.