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.

score 3 · Accepted Answer · 2012-02-03 04:06:48Z

3

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

edited Feb 3, 2012 at 4:06

answered Feb 3, 2012 at 3:41

user596075

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

2 Comments

R Saudlach Over a year ago

worked as soon as i removed the []'s; guess mySQL doesn't like square brackets. Thanks!!

user596075 Over a year ago

@HPatel I'm sorry, I assumed you were using SQL Server (don't know why I thought that). I've modified my post to reflect.

RobO · Accepted Answer · 2012-02-03 03:47:45Z

1

You're looking for a "GROUP BY" statement, where you tell the database how to group your data for counting. You need to group by both "state_abbr" and "state", so your query would look like:

SELECT
    states.state_abbr
    , states.state
    , COUNT(*)
FROM
    orgs
    INNER JOIN states ON states.state_abbr=orgs.state_abbr
GROUP BY
    state_name

answered Feb 3, 2012 at 3:47

RobO

412 bronze badges

Comments

user868322 · Accepted Answer · 2012-02-03 05:08:14Z

0

SELECT O.STATE_ABBR,S.[STATE],COUNT(S.[STATE]) FROM ORGS O INNER JOIN STATES S ON O.STATE_ABBR = S.STATE_ABBR GROUP BY O.STATE_ABBR,S.[STATE]

answered Feb 3, 2012 at 5:08

user868322

52 bronze badges

Collectives™ on Stack Overflow

SQL Join - Summing COUNT of Different Values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related