I am trying to use a count(..) to get the count value of matching records from a related table. I’m using MySQL 8.
The idea is simply to count the number of tasks assigned to each user.
If the user has NO tasks, they should still be listed, but with "0" as the task count (thus, I was thinking that a left join is what I needed). I’m hoping to use "u.*" because I will be adding additional fields to the output soon.
This is the SQL that I’m currently working with:
SELECT u.*, t.task_count
FROM users u
LEFT JOIN (
SELECT count(*) as task_count
FROM user_tasks
WHERE user_tasks.user_id = users.id
group by user_id
) AS t ON t.user_id = u.id
Here is the DB schema & some sample values:
create table user_tasks (
id integer primary key,
user_id integer not null
);
create table users (
id integer primary key,
name text not null
);
insert into user_tasks values (1, 1);
insert into user_tasks values (2, 2);
insert into user_tasks values (3, 1);
insert into user_tasks values (4, 1);
insert into user_tasks values (5, 1);
insert into user_tasks values (6, 2);
insert into users values (1, 'joe');
insert into users values (2, 'sally');
insert into users values (3, 'bob');
The output should be this, the count of how many tasks each user has:
1, 'joe', 4
2, 'sally', 2
3, 'bob', 0
At the moment I’m getting "Unknown column ‘users.id’" but I’m unclear why.
2
Answers
From your query, you can modify it like this to get the count from a correlated subquery:
For a
LEFT JOIN
, you can do something like this:However, instead of using
COUNT()
, useSUM(IF())
becauseCOUNT()
will returnbob=1
as there’s 1 row ofbob
. To clarify, if you run the query returning all columns and withoutGROUP BY
like this:you’ll get 1 row of
bob
in the results and that counted as 1:Demo fiddle