Search code examples
sqlcountjpql

SQL Foreign keys count

Table Person has columns id and parent_id.

I need get a table with columns id and children_count. How can it be performed? Why I can not write something like this? : 

SELECT 
    id, count(parent_id) AS children_count
FROM 
    Person
GROUP BY 
    parent_id;
Solution

  • You should outer join the table to itself to find all ids and its children and then calculate the count.

    select a.id parent,
       count(b.id) count_child
  from person a left outer join person b
    on a.id = b.parent_id
 group by a.id
 order by a.id;

    Demo at sqlfiddle.

    Same result can be achieved using right outer join also.

    select b.id parent,
       count(a.id) count_child
  from person a right outer join person b
    on a.parent_id = b.id
 group by b.id
 order by b.id;