SELECT em.Name, tr.DistanceTraveled
FROM Employee em,
     (SELECT SUM(distance) AS DistanceTraveled, EmployeeID FROM Trip GROUP
BY EmployeeID) tr
WHERE em.ID = 123 AND
      tr.EmployeeID = em.ID

(sorry i made this up quickly as my example is vastly more complex so excuse
any mistakes)

Here I think the the subquery does the SUM(distance) for each and every
employee rather than just the one i'm interested in. So really, what I
wanted was something like this:

SELECT em.Name, tr.DistanceTraveled
FROM Employee em,
     (SELECT SUM(distance) AS DistanceTraveled, EmployeeID FROM Trip
      WHERE EmployeeID = em.ID
      GROUP BY EmployeeID) tr
WHERE em.ID = 123

but this doesn't work.

Any hints are greatly appreciated!

Pete

SELECT em.Name, tr.DistanceTraveled
FROM Employee em
INNER JOIN
    (SELECT SUM(distance) AS DistanceTraveled, EmployeeID
        FROM Trip
        GROUP BY EmployeeID) tr
    ON tr.employeeid = em.ID
WHERE em.ID = 123

If you are using SQL 2005...

;WITH tr AS
    (SELECT SUM(distance) AS DistanceTraveled, EmployeeID
        FROM Trip
        GROUP BY EmployeeID)
SELECT em.Name, tr.DistanceTraveled
FROM Employee em
    INNER JOIN tr ON tr.employeeid = em.ID
WHERE em.ID = 123

--Peter

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--