SQL左连接3个表并使用COUNT和GROUP BY

Question

我有3个表，用户，分支和状态

表用户

name   code_branch
1   ANA      100
2   BEN      101
3   CARLA    102
4   DAN      100
5   ED       100

表分支

code_branch branch_name   code_state
1 100          BRANCH 100       A
2 101          BRANCH 101       A    
3 102          BRANCH 102       C
4 103          BRANCH 103       D
5 104          BRANCH 104       E
6 105          BRANCH 105       F

表州

code_state state_name
1   A            STATE A
2   B            STATE B
3   C            STATE C
4   D            STATE D
5   E            STATE E

和我想要的结果如下

state_name  total_user
1   STATE A      4
2   STATE B      0
3   STATE C      1
4   STATE D      0
5   STATE E      0

sql我

SELECT  s.code_state, COUNT(u.code_branch ) AS total
FROM ((states s
LEFT JOIN branches b ON s.code_state = b.code_state )
LEFT JOIN users u.code_branch = b.code_branch)
GROUP BY s.code_state;

，结果是

STATE    TOTAL
A       4
B       0
C       1 
D       0
E       0

我想在上面的查询结果中显示state_name而不是code_state。

然后在第一行像这样改变

SELECT  **s.state_name**, COUNT(u.code_branch ) AS total

和像这样

SELECT  **s.state_name, s.code_state** , COUNT(u.code_branch ) AS total

但是得到错误

Msg 8120, Level 16, State 1, Line 44
Column 's.state_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

尝试在select语句中添加组by ?

SELECT  **s.state_name, s.code_state** , COUNT(u.code_branch ) AS total 
Group by **s.state_name, s.code_state**