CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlaggregates
nckbrzg
nckbrzg

Reputation: 474

SQL filtering by aggregate functions

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.

Upvotes: 0

Views: 127

Answers (1)

Hart CO
Hart CO

Reputation: 34784

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.

Upvotes: 1

Related Questions