Check if value in column for all rows is exactly value
EXISTS should be used in preference to COUNT so it can return as soon as the first non matching row is found.
SELECT CASE WHEN NOT EXISTS(SELECT * FROM your_table WHERE status <> 2) THEN 'Y' ELSE 'N' END AS your_result You don't state RDBMS. You might need to append FROM DUAL onto the end of the above dependant on flavour.
select (select count(distinct status) from T) = 1 will return 1 or 0 (i.e. true or false) depending on whether all of the rows have the same value in Status or not. If you have to deal with NULL values in status:
select exists ( select status from T where status <> 2 or status is null)as StatusContainsOtherThanTwoOrNullValue