SQL filtering by aggregate functions

Question

Suppose I have an Employee table and a Department table. Every employee has one department.

Now, suppose I want to see all the departments --along with the employees of those departments-- that have exactly 1 employee.

Let's say my database looks like this.

Employee

EmployeeId  | EmployeeName  | DepartmentId
--------------------------------------------
1           | A Doe         | 1
--------------------------------------------
2           | B Doe         | 1
--------------------------------------------
3           | C Doe         | 1
--------------------------------------------
4           | D Doe         | 1
--------------------------------------------
5           | E Doe         | 2
--------------------------------------------

Department

DepartmentId  | DepartmentName
------------------------------------------
1             | Sales   
------------------------------------------                    
2             | HR
------------------------------------------

What I want to see is the following results:

DepartmentName  | EmployeeId  | EmployeeName  | EmployeeId_COUNT
------------------------------------------------------------------
HR              | NULL        | NULL          | 1
------------------------------------------------------------------
HR              | 5           | E Doe         | 1
------------------------------------------------------------------

Obviously, this can be done with multiple, separate queries.

But, does anyone know of a way to solve this with a single query?

My initial idea was to do something simple, like this

SELECT 
        d.DepartmentName 'Department'
        , e.EmployeeId 'EmployeeId'
        , e.EmployeeName
        , COUNT(e.EmployeeId) 'EmployeeId_COUNT'

FROM    Employee e
        LEFT JOIN Department d ON d.DepartmentId = e.DepartmentId

GROUP BY  GROUPING SETS ( 
                ( d.DepartmentName ), 
                ( d.DepartmentName, e.EmployeeId, e.EmployeeName) )
HAVING COUNT(e.EmployeeId) IN (1)

But this doesn't work, since every row that is not a grouped row (and some grouped rows as well) has an EmployeeId count of 1.

So, the results will look something like this:

DepartmentName  | EmployeeId  | EmployeeName  | EmployeeId_COUNT
------------------------------------------------------------------
HR              | NULL        | NULL          | 1
------------------------------------------------------------------
HR              | 5           | E Doe         | 1
------------------------------------------------------------------
Sales           | 1           | A Doe         | 1
------------------------------------------------------------------
Sales           | 2           | B Doe         | 1
------------------------------------------------------------------
Sales           | 3           | C Doe         | 1
------------------------------------------------------------------
Sales           | 4           | D Doe         | 1
------------------------------------------------------------------

This is not what I want, at all.

My ideal solution is simple (no complex inner queries, UNIONs, or INTERSECTs needed) and is easily generalizable to similar problems (SUM, MAX, MIN, etc. as well as other columns in more complex queries).

I should also note that I am doing this in T-SQL 2012, so any special functions or commands that might be useful are fair game.

Can you take another look at your sample data and desired results? They are not what you seem to describe. Why would HR be in the results with NULL for employee? Why is E Doe in HR in the results, but in Sales in your sample data? — Tab Alleman, Oct 15 '14 at 19:32
@TabAlleman HR with NULL for employee is in the results of my incorrect query because I have used the GROUPING SETS command. See http://msdn.microsoft.com/en-us/library/ms177673(v=sql.110).aspx It is in my intended results simply because I have defined them to be that way (it is convenient to solving the particular problem I am working on). The other issues are mistakes, and I will fix them shortly. — nckbrzg, Oct 15 '14 at 19:42

score 1 · Answer 1 · answered Oct 15 '14 at 19:37

You can use COUNT() with OVER() to get what you're after:

;with cte AS (SELECT *,COUNT(*) OVER(PARTITION BY DepartmentID) AS Department_CT
              FROM   Employee)
SELECT *
FROM cte a
JOIN  Department b
  ON a.DepartmentID = b.DepartmentID
WHERE Department_CT = 1

This allows you to retain the full detail as well as returning the aggregate for filtering.

SQL filtering by aggregate functions

1 Answers1