need Hierarchical query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need Hierarchical query

Author

Topic

venkatakondareddy
Starting Member

17 Posts

Posted - 2009-01-06 : 05:13:55

Hi all,
i want to know how to write a hierarchical query with two table. Here im givig two sample tables which i have taken.

Manager table-----------------------Emp Table
MgrNo--MgrId EmpNo--MgrNo---Sal
1 NULL 1 2 8000
2 1 2 2 8000
3 2 3 4 7000
4 2 4 10 6500
5 3 5 7 4000
6 4 6 2 7000
7 3 7 2 6500
8 5 8 2 6500
9 6 9 3 6000
10 4 10 5 5000
11 8 11 4 7000
12 9 12 3 6000
13 2 13 2 7500

Now i want to sum sal in the tree of MgrNo=2 and also count EmpNo's of MgrNo=2 From Emp table and Manager table.
My Expected Output is

MgrNo(Manager tab)--MgrId--Sum(sal)--Count(EmpNo). In this format.

Pleae can any one help me.

Regards
Venkat.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 05:25:42

select m.mgrno,m.mgrid ,s.sumsal,c.countemp
from manager m
inner join
(select count(empno) as countemp,mgrno from emp where mgrno = 2 )c on c.mgrno = m.mgrno
inner join
(select sum(sal)as sumsal,mgrno from emp where mgrno = 2 ) s on s.mgrno = m.mgrno
where m.mgrno = 2

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 05:58:52

select m.mgrno,m.mgrid,sum(e.sal) as 'sal',count(e.empno) as 'emp' from manager m
inner join emp e on (e.mgrno = m.mgrno and e.mgrno = 2)
where m.mgrid = 2
group by m.mgrno,m.mgrid

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 06:09:32

select count(empno) as Count_Emp,sum(sal)as Sum_Sal,mgrno from emp group by mgrno having mgrno=2

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-06 : 06:22:41

Or may Be like This,,,,,,

select m.mgrno,m.mgrid,sum(e.sal) as Sum_Sal,count(e.empno) as Count_Emp from manager m
inner join emp e on e.mgrno=m.mgrno
group by m.mgrno,m.mgrid
having m.mgrno=2

Thanks....

venkatakondareddy
Starting Member

17 Posts

Posted - 2009-01-06 : 06:28:21

Thanks for giving reply, but i need by using CTE. and also need all emp's sal and count of emp for who is the child of mgrid=2

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 07:32:23

Did u post ur tables structure correctly

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 07:59:22

Try This

declare @table table(MgrNo int,MgrId int,empno int ,sal numeric(18,2))
;with cte(MgrNo,MgrId,empno,sal)
as
(
select m.mgrno,m.mgrid,e.empno,e.sal from manager m left join emp e on (m.mgrno = e.mgrno)
where m.mgrid is null

union all

select m.mgrno,m.mgrid,e.empno,e.sal from manager m inner join cte c on (m.mgrid = c.mgrno)
inner join emp e on (m.mgrno = e.mgrno)
)
insert into @table
select distinct * from cte order by mgrno
select mgrno,count(distinct empno) as EmployeeCount,sum(sal) as SalarySum from @table group by mgrno

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources