SQL Server multi rows query with group by

Question

i have data like

╔══════╦══════════╦═══════╗
║ DEPT ║ EMPLOYEE ║ IMAGE ║
╠══════╬══════════╬═══════╣
║    1 ║        1 ║ Y     ║
║    1 ║        2 ║ N     ║
║    2 ║        3 ║ N     ║
║    2 ║        4 ║ N     ║
╚══════╩══════════╩═══════╝

from a query i want to get only departments with images = 'N'

so this should return dept 2

how will i do that in SQL Server Select statement

Answer 1

SELECT Distinct Dept from TableName where Image = 'Y'

But your example will bring back Dept 1 AND Dept 2, because your 2nd record for Dept 1 has a value of 'N' under Image. You won't just get back dept 2.

Answer 2

SELECT Dept
FROM   tableName
GROUP  BY DEPT
HAVING SUM(CASE WHEN [Image] = 'N' THEN 1 ELSE 0 END) = COUNT(*)

• SQLFiddle Demo