T-SQL II

Follow T-SQL I. Now assume some employees make phone calls. How to get the total amount of phone call time of the employees managed by a manager?

Say manager's empID is 1, then it's easy to get:

select SUM(phoneusage) AS PhoneUsage from Employee where mgrID = 1

What if you want the manager's name etc. to be returned on the same row? You could do this:

select A.EmpID, A.ManagerName, B.PhoneUsage FROM
(
    (select ID = 1, EmpID, (firstname + ' ' + lastname) AS ManagerName from Employee where empID = 1) A
    INNER JOIN
    (select ID = 1, SUM(phoneusage) AS PhoneUsage from Employee where mgrID = 1) B
    ON A.ID = B.ID
)

Or, you can first get the phone usage of employees for all the managers:

SELECT A.empID, (A.firstName + ' ' + A.lastName) as [ManagerName], B.phoneUsage FROM
(
    (select mgrID, SUM(phoneUsage) as phoneUsage from Employee group by mgrID) as B
    INNER JOIN Employee A
    ON A.empID = B.mgrID
)

Then you can add "AND A.empID = 1" to get the phone usage of employees managed by manager whose empID is 1:

SELECT A.empID, (A.firstName + ' ' + A.lastName) as [ManagerName], B.phoneUsage FROM
(
    (select mgrID, SUM(phoneUsage) as phoneUsage from Employee group by mgrID) as B
    INNER JOIN Employee A
    ON A.empID = B.mgrID
    AND A.empID = 1
)

The last method is better than the first method, since it does not use an artificial ID.