2 Replies - 1210 Views - Last Post: 16 August 2012 - 06:28 AM

#1 Thinus du Pisanie   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 115
  • Joined: 07-October 11

Select in a Select to count data needed

Posted 16 August 2012 - 12:32 AM

Hi guys I have a table Employees that contains following fields

ID
Employee_name
Employee_Surname
Employee_manager

How it works is
ID-----------Employee_name--------Employee_surname----------Employee_manager

1------------Jan------------------du Preez------------------2
2------------Koos-----------------Degenaar------------------None
3------------Piet-----------------Botha---------------------1
4------------May------------------Botha---------------------2

Meaning Jan and May Manager is Koos and Piet manager is Jan now what I want to achieve is I want to call a statement that Counts all Employees that reports to a manager and write it into a new table. What I got so far is this....

select 
   a.Employee_ID as ID, 
    a.Employee_Name as Name, 
    a.Employee_Surname as Surname,
    b.Employee_Name + ' ' +b.Employee_Surname as Manager 
from 
   dbo.lms_Employee  a, 
   dbo.lms_Employee  b 
where 
    a.Employee_Manager = b.Employee_ID  


Giving the result as following:
ID-----------Employee_name--------Employee_surname----------Employee_manager

1----------- Jan----------------- du Preez------------------ Koos Degenaar
4------------May------------------Botha--------------------- Koos Degenaar
3------------Piet-----------------Botha--------------------- Jan du Preez

What I want to display is

ID-----------Employee_name--------Employee_surname----------Employee_manager------Counted

1----------- Jan----------------- du Preez------------------ Koos Degenaar--------2 (Employees)
4------------May------------------Botha--------------------- Koos Degenaar--------2 (Employees)
3------------Piet-----------------Botha--------------------- Jan du Preez---------1 (Employees)

Then Write this result into a new table displaying like this:

ID---------Employee_ID--------Employee_name--------Employee_surname----------Employee_manager------Counted

1----------1----------------- Jan----------------- du Preez------------------ Koos Degenaar--------2 (Employees)
2----------4------------------May------------------Botha--------------------- Koos Degenaar--------2 (Employees)
3----------3------------------Piet-----------------Botha--------------------- Jan du Preez---------1 (Employees)

Any assistance will be mostly appreciated.
Thanks
Thinus

Is This A Good Question/Topic? 0
  • +

Replies To: Select in a Select to count data needed

#2 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7449
  • View blog
  • Posts: 15,442
  • Joined: 16-October 07

Re: Select in a Select to count data needed

Posted 16 August 2012 - 05:35 AM

First, get rid of the ancient "table,table where" syntax and use joins. It's deprecated and will go away, eventually. More here.

e.g.
select 
		a.Employee_ID as ID, a.Employee_Name as Name, a.Employee_Surname as Surname,
		b.Employee_Name + ' ' +b.Employee_Surname as Manager 
	from dbo.lms_Employee  a
		inner join dbo.lms_Employee  b 
			on a.Employee_Manager = b.Employee_ID



You should immediately see you have a problem. You're missing employees that have no Employee_Manager. ( I'm hoping None is NULL. )

This is solved with an outer join.
select 
		a.Employee_ID as ID, a.Employee_Name as Name, a.Employee_Surname as Surname,
		b.Employee_Name + ' ' +b.Employee_Surname as Manager 
	from dbo.lms_Employee  a
		left outer join dbo.lms_Employee b 
			on a.Employee_Manager = b.Employee_ID



What we're saying here is give me everything from a, even if there is no b.

To get a count, you'll want a sub query. Something like:
select Employee_Manager, count(*) as EmployeesForManager
	from dbo.lms_Employee
	group by Employee_Manager



You'll need to join that up as you see fit. It will be a sub query. Give it a shot.
Was This Post Helpful? 0
  • +
  • -

#3 Thinus du Pisanie   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 115
  • Joined: 07-October 11

Re: Select in a Select to count data needed

Posted 16 August 2012 - 06:28 AM

Ok cool this is nice I will definitely start looking into joins much easier.

I have one question thaw. I use None and not Null for myself it helps me and the Id is not auto incremented I named them unique like Emp1110001 and Emp1110002 and so on.

Now the statement underneath does exactly what I need and then the count(*) also but I want to merge the two statements that is the problem that I cant fix. Sorry I must have set my question better.


select
        a.Employee_ID as ID, a.Employee_Name as Name, a.Employee_Surname as Surname,
        b.Employee_Name + ' ' +b.Employee_Surname as Manager
    from dbo.lms_Employee  a
        inner join dbo.lms_Employee  b
            on a.Employee_Manager = b.Employee_ID



select Employee_Manager, count(*) as EmployeesForManager
    from dbo.lms_Employee
    group by Employee_Manager


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1