SQL Exercise: Count unoccupied rooms in each block and floor
25. From the following tables, write a SQL query to count the number of rooms that are unavailable in each block and on each floor. Sort the result-set on block floor, block code. Return the floor ID as "Floor", block ID as "Block", and number of unavailable as “Number of unavailable rooms".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
Sample Solution:
-- Counting the number of unavailable rooms per floor and block, and ordering the result by floor and blockcode
SELECT blockfloor AS "Floor",
blockcode AS "Block",
count(*) AS "Number of unavailable rooms"
-- FROM room table
FROM room
-- WHERE clause filters unavailable rooms
WHERE unavailable = 'true'
-- GROUP BY blockfloor and blockcode to count per floor and block
GROUP BY blockfloor, blockcode
-- ORDER BY blockfloor and blockcode for result sorting
ORDER BY blockfloor, blockcode;
Sample Output:
Floor | Block | Number of unavailable rooms
-------+-------+---------------------------
1 | 2 | 1
2 | 1 | 1
2 | 2 | 1
3 | 1 | 1
3 | 3 | 1
4 | 1 | 1
4 | 3 | 1
(7 rows)
Explanation:
The said query in SQL that selects the blockfloor and blockcode columns from the room table and counts the number of unavailable rooms.
The uses of WHERE clause only includes rows where the unavailable column has a value of 'true'.
The GROUP BY clause groups the results by blockfloor and blockcode, and the ORDER BY clause sorts the output by blockfloor and blockcode.
Alternative Solutions:
Using CASE Statement for Conditional Count:
-- Counting the number of unavailable rooms per floor and block using CASE statement
-- Selecting the blockfloor, blockcode, and counting the rooms where unavailable is true
SELECT blockfloor AS "Floor",
blockcode AS "Block",
count(CASE WHEN unavailable = 'true' THEN 1 END) AS "Number of unavailable rooms"
-- FROM room table
FROM room
-- GROUP BY blockfloor and blockcode to count per floor and block
GROUP BY blockfloor, blockcode
-- HAVING clause filters groups with counts greater than 0 for unavailable rooms
HAVING count(CASE WHEN unavailable = 'true' THEN 1 END) > 0
-- ORDER BY blockfloor and blockcode for result sorting
ORDER BY blockfloor, blockcode;
Explanation:
This solution uses a CASE statement within the COUNT function to conditionally count unavailable rooms. The query groups results by "blockfloor" and "blockcode" and orders them accordingly.
Using SUM with Boolean Expression for Counting:
-- Counting the number of unavailable rooms per floor and block using SUM with boolean expression
-- Selecting the blockfloor, blockcode, and summing rooms where unavailable is true
SELECT blockfloor AS "Floor",
blockcode AS "Block",
sum(CASE WHEN unavailable = 'true' THEN 1 ELSE 0 END) AS "Number of unavailable rooms"
-- FROM room table
FROM room
-- GROUP BY blockfloor and blockcode to count per floor and block
GROUP BY blockfloor, blockcode
-- HAVING clause filters groups with sums greater than 0 for unavailable rooms
HAVING sum(CASE WHEN unavailable = 'true' THEN 1 ELSE 0 END) > 0
-- ORDER BY blockfloor and blockcode for result sorting
ORDER BY blockfloor, blockcode;
Explanation:
This solution employs the SUM function with a boolean expression to count unavailable rooms. The query groups results by "blockfloor" and "blockcode" and orders them accordingly.
Practice Online
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Count the number of rooms in each block on each floor.
Next SQL Exercise: Find the floor with the most rooms available.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.