CODEWITHSUNDEEP
sqlsqlite
Ahmad Manoochehri
Ahmad Manoochehri

Reputation: 1

SQL select & count from two tables

I have two tables, "users" & "projects"

users table:

id username

1 sam

2 peter

3 andy

projects table:

id title uid

1 pr1 0

2 pr2 1

3 pr3 2

4 pr4 1

projects.uid represents the user id who will do the job and 0 means this project is not assigned to any user yet.

I want a query to count and group projects based on users & return something like this:

uid project_count

0 1

1 2

2 1

3 0

Upvotes: 0

Views: 240

Answers (3)

Sachin Kainth
Sachin Kainth

Reputation: 46750

select u.id, count(p.uid)
from users u
inner join project p 
on u.id = p.uid
group by p.uid

Upvotes: 0

Ahmad Manoochehri
Ahmad Manoochehri

Reputation: 1

Thanks so much for your answers but none of them seems to return right rows,

here is my newly found answer:

SELECT users.id,(SELECT COUNT(1) FROM projects WHERE projects.user_id = users.id) as 'count' FROM users ORDER BY id

I'm not pretty sure about it's performance but currently it works...

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

Since you are not returning username, you only need the projects table:

select u.id, count(*) as project_count
from users u
left outer join projects p on u.id = p.uid 
group by u.id
order by u.id

If you want the username too, you can do this:

select u.id, u.username, count(*) as project_count
from users u
left outer join projects p on u.id = p.uid
group by u.id, u.username
order by u.id

Upvotes: 2

Related Questions