CODEWITHSUNDEEP
mysqlsqlsubquery
user2681263
user2681263

Reputation: 21

SQL Query For finding no of child departments for each department

I would like to write a query which will return Dept Name and respective count of its children departments.

I am having following table structure:

Table Name# Department
Dept_ID(Pk) Dept_name parent_dept_id(references dept_id)

Dummy data would be 

DEPT_ID            DEPT_NAME            PARENT_DEP_ID   
1              HEAD OFFICE               1
2              ADMINISTRATION            1
3              ARTS                      2
4              SCIENCE                   2
12             Agriculture               2
5              COMP. SCIENCE             4

Your help will be appreciated.

Upvotes: 2

Views: 245

Answers (4)

rocks
rocks

Reputation: 190

select DEPT_ID,DEPT_NAME,coalesce(dt.child,0) CHILD 
from department d
left join
(
  select PARENT_DEP_ID,count(1) child 
  from department 
  where DEPT_ID <> PARENT_DEP_ID 
  group by 1
)dt
on d.DEPT_ID=dt.PARENT_DEP_ID;

Upvotes: 0

Romesh
Romesh

Reputation: 2274

Here is the SQLFiddel Demo

Below is the Query which you can try

select P.DEPT_ID,P.DEPT_NAME,count(C.DEPT_ID)
  from Department P
  Left join Department C
    on p.DEPT_ID = C.PARENT_DEP_ID
   and p.DEPT_ID <> C.DEPT_ID
 group by P.DEPT_ID,P.DEPT_NAME

Upvotes: 1

user2674996
user2674996

Reputation:

try this

select *,count(ChildDepart.PARENT_DEP_ID)    from Department as ParentDepart inner join
Department as ChildDepart in ParentDepart.DEPT_ID = ChildDepart.PARENT_DEP_ID

Upvotes: 0

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can try this-

Select count(*) as count, P.* from Department P 
  LEFT JOIN Department C ON p.dept_id = C.parent_dep_id
  GROUP BY C.parent_dep_id

Upvotes: 0

Related Questions