SQL exercises on hospital Database: Count the number of available rooms in each floor

SQL hospital Database: Exercise-23 with Solution

23. From the following table, write a SQL query to count the number of available rooms in each floor. Sort the result-set on block floor. Return floor ID as "Floor" and count the number of available rooms as "Number of available rooms".

Sample table: room

Sample Solution:

SELECT blockfloor AS "Floor",
       count(*) "Number of available rooms"
FROM room
WHERE unavailable='false'
GROUP BY blockfloor
ORDER BY blockfloor;

Sample Output:

 Floor | Number of available rooms
-------+---------------------------
     1 |                         8
     2 |                         7
     3 |                         7
     4 |                         7
(4 rows)

Practice Online

E R Diagram of Hospital Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to count the number of available rooms in each block. Sort the result-set on ID of the block. Return ID of the block as “Block”, count number of available rooms as “Number of available rooms”.
Next: From the following table, write a SQL query to count the number of available rooms for each floor in each block. Sort the result-set on floor ID, ID of the block. Return the floor ID as "Floor", ID of the block as "Block", and number of available rooms as "Number of available rooms".