CODEWITHSUNDEEP
mysql
Tariq
Tariq

Reputation: 2871

select count from same table

I have a tasks table with a task_id column and parent_id column for each task.
I am trying to build a query that returns for each task_id, the number of times this id shows in the parent id column.
I tried this query: 

SELECT
     task_id, parent_id, 
     (SELECT COUNT( * ) 
      FROM  `tasks` 
      WHERE parent_id = task_id) AS count_parents 
FROM tasks

This query didn't work. I hope the question is clear enough.

Upvotes: 1

Views: 2415

Answers (4)

ysth
ysth

Reputation: 98388

If you want to include all tasks whether they are ever a parent or not,

select tasks.task_id,count(parent.parent_id)
from tasks
left join tasks parent on tasks.task_id=parent.parent_id
group by tasks.task_id;

If you want only tasks that are parents, it is trivial:

select parent_id,count(*)
from tasks
group by parent_id;

(the latter possibly needing a where parent_id is not null or such, depending how you represent tasks that have no parent).

Upvotes: 5

Lexsoul
Lexsoul

Reputation: 155

Try this:

select task_id,parent_id, count(parent_id) as count_parents 
from `tasks` 
group by task_id,parent_id;

In this case you need a group by, hope it helps.

Upvotes: 1

Puppet Master 3010
Puppet Master 3010

Reputation: 261

SELECT t2.task_id, t2.parent_id, 
     (SELECT COUNT( * ) FROM  `tasks` t1 
         WHERE t1.parent_id = t2.task_id) AS count_parents 
 FROM tasks t2

I think from your question

Upvotes: 0

Taryn
Taryn

Reputation: 247670

It seems like you want this:

SELECT task_id, 
  parent_id, 
  COUNT(parent_id) AS count_parents
FROM tasks
GROUP BY task_id, parent_id

Upvotes: 1

Related Questions