Looking for SQL to do a Sum of a Subquery

Question

I'm not a wiz when it comes to Subqueries. I'm trying to get the SQL script below to give the correct results, and with I'm doing something wrong, or I'm misinterpreting the results. Here is my SQL.

select Horizontal  Horizontal, sum(headcount)  Headcount, 
  sum(fte)  FTE, BE, Street, City, State, Zip, Country, 
  SQ_FT, O_L, Comp_Code, Entity
FROM
    (SELECT t.Horizontal Horizontal, 
        COUNT(d.SIGNEDAU) Headcount, 
        COUNT(d.SIGNEDAU) * t.[AU Distribution to Services] FTE, 
        a.[BE #] BE, d.ADDRESSLINE1 Street, d.CITY, 
        d.STATE, d.ZIP, d.COUNTRY,  
        a.FT2 SQ_FT, a.[O/L] O_L, a.[Comp Code], a.ENTITY
     FROM dbo.HR_Data d
        join dbo.Service_Taxonomy t
            ON d.SIGNEDAU = t.[AU Code] 
        left join dbo.All_Active a 
            ON d.CITY = a.City 
               AND d.STATE = a.ST 
               AND d.ADDRESSLINE1 = a.Street
     GROUP BY d.SIGNEDAU, t.[AU Distribution to Services], 
        a.[BE #], a.FT2, a.[O/L], a.[Comp Code],
        a.ENTITY, t.[Critical Y/N], t.Horizontal, t.[Level 1], 
        d.ADDRESSLINE1, d.CITY, d.STATE, d.COUNTRY, d.ZIP, 
        d.HR_STATUS, d.EMPLOYEESTATUS
     HAVING d.HR_STATUS = N'A' and d.EMPLOYEESTATUS = N'A') sub
Group by Horizontal, FTE, BE, Street, 
   City, State, Zip, Country, SQ_FT, 
   O_L, Comp_Code, Entity

I would expect the sum of Headcount and FTE, with the Group By, to merge these two rows into one, and yield a Headcount total of 10 and a FTE total of 5. For some reason, which I don't completely understand, the SQL above seems to NOT be summing and NOT be grouping. What am I missing?

Answer 1

Exclude FTE from GROUP BY and SELECT