Applying Multiple Conditions on the Same Column with AND Operator in SQL Server 2008 R2

Counting Occurrences of a Column Value in SQL Without Repetition