Kazem Kazem - 1 year ago 42
SQL Question

Access SQL select count number of specific field from one table join another table

I have these two table in my access file (I make it simple)
Table 1: Users:

ID User_Code User_Name
== ========= ==========
1 1111 John
2 2222 Alex
3 3333 Tom

Table 2 GB:

ID User_Code First(is Boolean)
== ========= =================
1 1111 Yes
2 2222 Yes
3 1111 Yes
4 1111 Yes

I want a SQL query that result like this table:

User_Name CountNum
========= =========
John 3
Alex 1
Tom 0

I know I must use inner join and distinct and count function but don't know exactly how???
I appreciate for your answers.

Answer Source

It is unclear where the distinct comes in. This is just a join and group by:

select u.user_name, count(gb.user_code)
from users as u left join
     on u.user_code = gb.user_code
group by u.user_name;

Note: The user of user_code for the join key is suspect. The Users table has an id. Normally that would be used for the join.