SQL Exercise: Count the number of available rooms in each floor
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
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 floor and ordering the result by blockfloor
SELECT blockfloor AS "Floor",
count(*) AS "Number of available rooms"
-- FROM room table
FROM room
-- WHERE clause filters available rooms
WHERE unavailable = 'false'
-- GROUP BY blockfloor to count per floor
GROUP BY blockfloor
-- ORDER BY blockfloor for result sorting
ORDER BY blockfloor;
Sample Output:
Floor | Number of available rooms
-------+---------------------------
1 | 8
2 | 7
3 | 7
4 | 7
(4 rows)
Explanation:
The said query in SQL that retrieves the number of available rooms on each floor of a block in a building.
This query counts the number of rows that satisfy the WHERE condition, and the WHERE clause filters to include only rows where the unavailable column is 'false'.
The GROUP BY clause groups the data by the blockfloor column, so that the count of available rooms is computed for each distinct floor.
The ORDER BY clause sorts the output by the blockfloor column in ascending order.
Alternative Solutions:
Using CASE Statement for Conditional Count:
-- Counting the number of available rooms per floor using CASE statement
SELECT blockfloor AS "Floor",
count(CASE WHEN unavailable = 'false' THEN 1 END) AS "Number of available rooms"
-- FROM room table
FROM room
-- GROUP BY blockfloor to count per floor
GROUP BY blockfloor
-- ORDER BY blockfloor for result sorting
ORDER BY blockfloor;
Explanation:
This solution uses a CASE statement within the COUNT function to conditionally count available rooms. The query groups results by "blockfloor" and orders them accordingly.
Using SUM with Boolean Expression for Counting:
-- Counting the number of available rooms per floor using SUM with boolean expression
SELECT blockfloor AS "Floor",
sum(CASE WHEN unavailable = 'false' THEN 1 ELSE 0 END) AS "Number of available rooms"
-- FROM room table
FROM room
-- GROUP BY blockfloor to count per floor
GROUP BY blockfloor
-- ORDER BY blockfloor for result sorting
ORDER BY blockfloor;
Explanation:
This solution employs the SUM function with a boolean expression to count available rooms. The query groups results by "blockfloor" 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 available rooms in each block.
Next SQL Exercise: Count the number of rooms in each block on each floor.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.