MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table user_tasks (
    id int primary key,
    user_id int not null
);

create table users (
    id int primary key,
    name text not null
);

insert into user_tasks values (1, 1),(2, 2),(3, 1),(4, 1),(5, 1),(6, 2);

insert into users values (1, 'joe'),(2, 'sally'),(3, 'bob');

SELECT * FROM users;
SELECT * FROM user_tasks;

Records: 6  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0




id
name


1
joe

2
sally

3
bob




id
user_id


1
1

2
2

3
1

4
1

5
1

6
2


SELECT u.*, (
  SELECT count(*)
  FROM user_tasks
  WHERE user_tasks.user_id = u.id
  group by user_id
) as task_count 
FROM users u;


id
name
task_count


1
joe
4

2
sally
2

3
bob
null


/*this doesn't work because bob task count is suppose to be 0 but since 
it's a left join, bob still get counted as 1 row*/
SELECT u.id, u.name, count(*) as task_count 
  FROM users u
    LEFT JOIN user_tasks ut
  ON ut.user_id = u.id
  group by u.id, u.name;

SELECT *
  FROM users u
    LEFT JOIN user_tasks ut
  ON ut.user_id = u.id;


id
name
task_count


1
joe
4

2
sally
2

3
bob
1




id
name
id
user_id


1
joe
5
1

1
joe
4
1

1
joe
3
1

1
joe
1
1

2
sally
6
2

2
sally
2
2

3
bob
null
null


/*replace COUNT() with SUM(IF()) so if the value is null, return 0 else return 1*/

SELECT u.id, u.name, sum(if(ut.id is null,0,1))
  FROM users u
    LEFT JOIN user_tasks ut
  ON ut.user_id = u.id
  group by u.id, u.name;


id
name
sum(if(ut.id is null,0,1))


1
joe
4

2
sally
2

3
bob
0