mysql number of people assigned to a task

Question

I have the following tables...

Tasks

People

tasks_people

... and I want to get a list of tasks, with a count of people assigned to them. Note that a person can be assigned to a task more than once (on different dates). For some reason I dont seem to be able to manage it.

Thanks for any help!

Answer 1

I cant see your table structure, but you can try selecting the distinct values from tasks_people (distinct taskid, personid) and then count and group by.

Answer 2

select task_id, count(distinct people_id) as people_assigned_to_task
from tasks_people
group by task_id

and if count + distinct misbehaves (i.e. takes too long), you can try this:

select task_id, 
(select count(*) from 
      (
          select distinct people_id from tasks_people i 
          where i.task_id = o.task_id
      ) 
) as people_assigned_to_task
from tasks_people o
group by task_id

Answer 3

if you want same person's many assignment to be counted as 1 do:

select tasks.task_id, tasks.title, count(distinct tasks_people.people_id) 
as p_counter
from tasks left join tasks_people
on tasks.task_id = tasks_people.task_id
group by tasks.task_id

Otherwise simply count() it

select tasks.task_id, tasks.title, count(tasks_people.people_id) as p_counter
from tasks left join tasks_people
on tasks.task_id = tasks_people.task_id
group by tasks.task_id