Question: select all the departments having students with even roll number

```
Dept No     Roll No   Student Name 

 1         1          lee 
 1         2          scott
 2         2          scott
 2         4          smith
 1         4          smith

```

This should result in DEpt no 2 as it has only students with roll number divisible by 2

Answer: 
Another(imo easy and lightweight) way is using `NOT EXISTS` and `DISTINCT`:

```
SELECT DISTINCT [Dept No]
FROM dbo.TableName t
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.TableName t2
    WHERE t.[Dept No] = t2.[Dept No]
      AND t2.[Roll No] % 2 = 1
)

```

`[**Demo**](http://sqlfiddle.com/#!6/f4ba1/1/0)`

If there is no odd number all must be even.
You can use `GROUP BY` with `HAVING` like this.

**Query**

```
SELECT [Dept No]
FROM departments
GROUP BY [Dept No]
HAVING SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1
AND SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END) = 0

```

**Explanation**

The query returns the departments if there a rollno which is even using `SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1`. If there is any rollno with odd roll no, `SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END)` will return non zero sum and that department will be excluded.