The COUNT (*) return the number of rows per group as defined in the GROUP BY clause.

COUNT (expression) return the number of rows per group where expression is NOT NULL. Since DECODE (occupied, 'Y', 1) returns either 1 or NULL then COUNT (DECODE (occupied, 'Y', 1)) returns the count of rows per group where occupied is 'Y'.

Both of these functions can be used as part of other expressions, e.g. you could use COUNT (*) + 10 to return the number of rows per group plus 10.

All the outer DECODE expression does is compare the number of rows per group where occupied = 'Y' with 0 (if true then all houses are 'Unoccupied') and then with the total number of rows (= houses) per group (if true then all houses are 'Occupied'). Any rows which fall into neither of these buckets are 'Partially Occupied' hence this is the 'ELSE' expression in the DECODE.

All I do when faced with expressions I don't get is expand them. I might do something like this...

Code:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

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

STREET     OCCUPIED      TOTAL STATUS
-------- ---------- ---------- ------------------
Street A          4          4 Occupied
Street B          2          3 Partially Occupied
Street C          0          2 Unoccupied

SQL>
Is that of any help?