Gimbl Gimbl - 1 year ago 39
SQL Question

Filter query with a GROUP BY based on column not in GROUP BY statement

Given the following table structure and sample data:


+-------------+------+-------------+
| EmployeeID | Name | WorkWeek |
+--------------+-------+-----------+
| 1 | A | 1 |
| 2 | B | 1 |
| 2 | B | 2 |
| 3 | C | 1 |
| 3 | C | 2 |
| 4 | D | 2 |
+--------------+-------+-----------+


I am looking to select all employees that only worked week 1 (so in this example, only
employeeid = 1
would be returned. I am able to get the data with the following query:

SELECT EmployeeId, Name
FROM SomeTable
GROUP BY EmployeeId, Name
HAVING SUM ( WorkWeek ) = 1;


To me, the
HAVING SUM( WorkWeek ) = 1
is a hack and this should be handled with some form of a
GROUP BY
and
COUNT
but I cannot wrap my head around how that query would be structured.

Any help would be useful and enlightening.

Answer Source

HAVING SUM( WorkWeek ) = 1 may work for week 1 or 2, but will fail for week 3 (since 1+2 = 3).
Use NOT EXISTS operator with a subquery instead:

SELECT  EmployeeId, Name
FROM    SomeTable t1
WHERE NOT EXISTS (
   SELECT * FROM  SomeTable  t2
   WHERE t1.EmployeeId = t2.EmployeeId
     AND t2.WorkWeek  <> 1
)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download