SQL query within SQL query on sum

Question

I have two tables, Employees and EmployeeVacations. I am trying to do a SQL query to get the sum of how much vacation time each employee has taken and their current balance as of today. Here is my current SQL query:

SELECT
    e.PIN,
    e.FirstName,
    e.LastName,
    e.Uniform,
    e.AL_Cap,
    ev.Value AS '10/1 Balance',
    (SELECT
        SUM(value)
    FROM EmployeeVacations
    WHERE CreationDate >= '2016-10-01'
    AND Vacation_Type = 'Taken'
    AND Vacation_Kind = 'AL'
    AND EmployeeId = 13)
    AS Taken
FROM    employees e,
        EmployeeVacations ev
WHERE e.Id = ev.EmployeeId
AND ev.IsHistory = 0
AND ev.Vacation_Type = 'Forward'
AND ev.Vacation_Kind = 'AL'
AND EmployeeId = 13
ORDER BY e.LastName, e.FirstName

This works if I pick a single employee. If I remove the "where EmployeeId = 13", I get a list of all the employees with the sum of everyone's total vacation in every row (like 1,300 hours). How do I break it down so it only shows the Taken for each employee specifically?

Answer 1

Just guessing that you also might want the sum rather than the single forward records... Here is a query that aggregates EmployeeVacations per EmployeeId:

select
  e.pin,
  e.firstname,
  e.lastname,
  e.uniform,
  e.al_cap,
  ev.forward_sum as "10/1 balance",
  ev.taken_sum as taken
from employee e
left join
(
  select     
    employeeid,
    sum(case when vacation_type = 'Forward' 
             and ishistory = 0 then value else 0 end) as forward_sum,
    sum(case when vacation_type = 'Taken' 
             and creationdate >= '20161001' then value else 0 end) as taken_sum,
  from employeevacations
  where vacation_kind = 'AL'
  group by employeeid
) ev on ev.employeeid = e.employeeid
order by e.lastname, e.firstname;

Please ...

• use explicit joins instead of the pre-1992 comma-separated joins for readability and for being less prone to errors.
• use double quotes for alias names; single quotes are for string literals.
• use 'yyyymmdd' for dates; it is the supported date literal format in SQL Server.

Answer 2

You need a corelated query, where the subquery uses a value from the "parent" query.

SELECT e.PIN ...
    (select SUM(value) .... WHERE EmployeeID = e.id) as taken
                                               ^^^^^

Note that these can be very inefficient, since the inner query has to be executed once for every row of the parent query. IN a lot of cases, you may be better off re-writing as a conventional JOIN query with appropriate grouping.