SQL联接-求和COUNT个不同的值 [英] SQL Join - Summing COUNT of Different Values

问题描述

比方说，我有两个表组织和州 orgs是(o_ID，state_abbr)，州是(state_abbr，state)

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

我想创建一个视图，显示每个状态下的o_ID计数:

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

我将使用哪种SQL语句? 我尝试过的那些仅显示第一个状态并对所有计数求和.

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

谢谢.

推荐答案

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

这篇关于SQL联接-求和COUNT个不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！