MYSQL How to do a count using a substring and left joining content.

Question

I am trying to figure out how to do this query. I have looked all over and with little sucess.

I have 2 tables one is called numbers the other is called area_code Here is what they look like. I know that there will need to be substring(phoneNumbers,1,3)

Table: numbers

phoneNumber 
4062081234
4062345453
2141234454
2101234555

Table: area_code

area_code       state      stateLong
214               TX         Texas
210               TX         Texas
406               MT         Montana

Desired output:

stateLong              count of phoneNumbers per state
Montana                2
Texas                  2

Thank you so much for the help.

sgeddes · Accepted Answer · 2014-05-15 00:28:27Z

2

You can join using left():

select ac.stateLong, count(*)
from area_code ac
    join numbers n on ac.area_code = left(n.phoneNumber,3)
group by ac.stateLong

In your title, you mention the need for an outer join, if you want to return all the states even if they don't have phonenumbers:

select ac.stateLong, count(*)
from area_code ac
   left join numbers n on ac.area_code = left(n.phoneNumber,3)
group by ac.stateLong

answered May 15, 2014 at 0:28

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL How to do a count using a substring and left joining content.

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related