CODEWITHSUNDEEP
mysqldatabase
michael
michael

Reputation: 4483

mysql number of people assigned to a task

I have the following tables...

Tasks alt text http://img260.imageshack.us/img260/3695/screenshotbg.png

People alt text http://img685.imageshack.us/img685/6445/screenshot1f.png

tasks_people alt text http://img260.imageshack.us/img260/1061/screenshot2r.png

... 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!

Upvotes: 0

Views: 136

Answers (3)

Tzury Bar Yochay
Tzury Bar Yochay

Reputation: 9004

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

Upvotes: 2

davek
davek

Reputation: 22925

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166476

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.

Upvotes: 0

Related Questions