Dapi you are missing a FROM in there (previous attempt had 3 where clauses!). Otherwise that looks OK for occupation status by row.

To group by street as post appears to require, try...
Code:
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE table_name (
  2    street VARCHAR2 (8), 
  3    house_no NUMBER (1), 
  4    occupied VARCHAR2 (1));

Table created.

SQL> INSERT INTO table_name VALUES ('Street A', '1', 'Y');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street A', '2', 'Y');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street A', '3', 'Y');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street A', '4', 'Y');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street B', '2', 'Y');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street B', '4', 'N');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street B', '5', 'Y');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street C', '3', 'N');

1 row created.

SQL> INSERT INTO table_name VALUES ('Street C', '5', 'N');

1 row created.

SQL> SELECT street, 
  2         DECODE (COUNT (DECODE (occupied, 'Y', 1)), 
  3           0, 'Unoccupied', 
  4           COUNT (*), 'Occupied', 
  5           'Partially Occupied') status
  6  FROM   table_name
  7  GROUP BY street;

STREET   STATUS
-------- ------------------
Street A Occupied
Street B Partially Occupied
Street C Unoccupied

SQL>