CODEWITHSUNDEEP
mysqlsqlleft-join
ezles88
ezles88

Reputation: 179

SQL LEFT JOIN 3 TABLES AND USING COUNT AND GROUP BY

I have 3 tables, users, branches and states

TABLE USERS

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

TABLE BRANCHES

    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

TABLE STATES

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

and i want the result as per below

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

My 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;

and the result is

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

I Want to show state_name instead of code_state in my query result above.

then i change on the first line like this

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

and like this

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

but get the error

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.

Upvotes: 0

Views: 432

Answers (1)

Jacqui Huang
Jacqui Huang

Reputation: 46

Try adding the group by in the select statement?

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

Upvotes: 2

Related Questions