SQL Join - Summing COUNT of Different Values

Question

Let's say I have two tables orgs and states orgs is (o_ID, state_abbr) and states is (state_abbr, state)

o_ID     state_abbr
1        CT
2        OH
3        OH

state_abbr state
CT         Connecticut
OH         Ohio
Alabama    AL

I would like to create a view that shows the count of o_ID in each state:

state_abbr      state       count
CT              Connecticut 1
OH              Ohio        2

What kind of SQL Statement would I use? The ones that I have tried only show the first state and sum all the counts.

Thank you in advance.

Answer 1

select
    o.state_abbr,
    s.state,
    o.[count]
from states s
inner join
(
    select state_abbr, count(*) as count
    from orgs
    group by state_abbr
) o
on s.state_abbr = o.state_abbr