CODEWITHSUNDEEP
sqlsql-servert-sql
codiaak
codiaak

Reputation: 1540

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.

Upvotes: 0

Views: 1846

Answers (2)

krokodilko
krokodilko

Reputation: 36087

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
)

Upvotes: 2

ATC
ATC

Reputation: 957

Actually, that's exactly why the having clause is for - to filter records according to the aggregated values.

From w3schools sql tutorial:

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Upvotes: 0

Related Questions