SQL Exercise: Count the number of rooms in each block on each floor
24. From the following tables, 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".
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 block, and ordering the result by floor and blockcode
SELECT blockfloor AS "Floor",
blockcode AS "Block",
count(*) AS "Number of available rooms"
-- FROM room table
FROM room
-- WHERE clause filters available rooms
WHERE unavailable = 'false'
-- 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 available rooms
-------+-------+---------------------------
1 | 1 | 3
1 | 2 | 2
1 | 3 | 3
2 | 1 | 2
2 | 2 | 2
2 | 3 | 3
3 | 1 | 2
3 | 2 | 3
3 | 3 | 2
4 | 1 | 2
4 | 2 | 3
4 | 3 | 2
(12 rows)
Explanation:
The said query in SQL that selects the blockfloor and blockcode columns and counts the number of available rooms by grouping the data by blockfloor and blockcode where the value of unavailable column is 'false'.
The WHERE clause filters the data by only selecting rows where the value of the unavailable column is 'false'.
The GROUP BY clause groups the data by blockfloor and blockcode, which allows the count of available rooms to be aggregated by these columns.
The ORDER BY clause orders the results by blockfloor and blockcode in ascending order.
Alternative Solutions:
Using CASE Statement for Conditional Count:
-- Counting the number of available rooms per floor and block using CASE statement
SELECT blockfloor AS "Floor",
blockcode AS "Block",
count(CASE WHEN unavailable = 'false' THEN 1 END) AS "Number of available rooms"
-- FROM room table
FROM room
-- 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;
Explanation:
This solution uses a CASE statement within the COUNT function to conditionally count available rooms. The query groups results by "blockfloor" and "blockcode" and orders them accordingly.
Using SUM with Boolean Expression for Counting:
-- Counting the number of available rooms per floor and block using SUM with boolean expression
SELECT blockfloor AS "Floor",
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 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;
Explanation:
This solution employs the SUM function with a boolean expression to count available 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 available rooms in each floor.
Next SQL Exercise: Count unoccupied rooms in each block and floor.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.