I want to compare row count of two tables and then return
CASE WHEN (select count(*) from table1)=(select count(*) from table2)
END AS RowCountResult
you have to remove :
Otherwise it will consider the result of the Case-When for each row of this FROM clause.