SQL Exercise: Count the number of available rooms in each block
22. 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".
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 available rooms per block and ordering the result by blockcode
SELECT blockcode AS "Block",
count(*) AS "Number of available rooms"
-- FROM room table
FROM room
-- WHERE clause filters available rooms
WHERE unavailable = 'false'
-- GROUP BY blockcode to count per block
GROUP BY blockcode
-- ORDER BY blockcode for result sorting
ORDER BY blockcode;
Sample Output:
Block | Number of available rooms
-------+---------------------------
1 | 9
2 | 10
3 | 10
(3 rows)
Explanation:
The said query in SQL that selects the block code and the number of available rooms for each block where the "unavailable" column in the "room" table is set to 'false'. The results are grouped by block code and ordered by block code.
The "count" function counts the number of rows in the "room" table where the "unavailable" column is set to 'false'. This count is aliased as "Number of available rooms".
The "WHERE" statement filters the results to only include rows where the "unavailable" column is set to 'false'.
The "GROUP BY" statement groups the results by block code.
The "ORDER BY" statement orders the results by block code in ascending order.
Alternative Solutions:
Using CASE Statement for Conditional Count:
-- Counting the number of available rooms per block using CASE statement
SELECT blockcode AS "Block",
count(CASE WHEN unavailable = 'false' THEN 1 END) AS "Number of available rooms"
-- FROM room table
FROM room
-- GROUP BY blockcode to count per block
GROUP BY blockcode
-- ORDER BY blockcode for result sorting
ORDER BY blockcode;
Explanation:
This solution uses a CASE statement within the COUNT function to conditionally count available rooms. The query groups results by "blockcode" and orders them accordingly.
Using SUM with Boolean Expression for Counting:
-- Counting the number of available rooms per block using SUM with boolean expression
SELECT blockcode AS "Block",
sum(CASE WHEN unavailable = 'false' THEN 1 ELSE 0 END) AS "Number of available rooms"
-- FROM room table
FROM room
-- GROUP BY blockcode to count per block
GROUP BY blockcode
-- ORDER BY blockcode for result sorting
ORDER BY blockcode;
Explanation:
This solution employs the SUM function with a boolean expression to count available rooms. The query groups results by "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: Find patients without an appointment.
Next SQL Exercise: Count the number of available rooms in each floor.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.