Sql availability query error I have a query to fetch the available rooms from 2 tables, roomtypes and reservations, as follows: 

Roomtypes has the following columns 

id,
product_id,
type,
total_no_of_rooms,
created_at,
updated_at


Reservation has the following columns 

id, 
roomtype_id, 
customer_id, 
check_in, 
check_out, 
no_of_rooms, 
created_at, 
updated_at,


This is my sql query:

SELECT rt.id, rt.type, rt.total_no_of_rooms, 
    SUM(r.no_of_rooms) as "reserved rooms", 
    rt.total_no_of_rooms - SUM(r.no_of_rooms) as "rooms available"  
FROM Roomtypes rt 
    LEFT OUTER JOIN Reservations r ON r.roomtype_id = rt.id 
        AND '2018-04-11 10:00:00' &gt;= r.check_in 
        AND '2018-04-12 09:59:59' &lt;= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms


When there is a reserved room in the given date, the column shows values to rooms available and rooms reserved. But if no rooms are reserved, the column shows null as values where as I need to get the rooms available as number of rooms available and reserved rooms as 0. How can I get this?
Try using the following query (Considering you are using mysql):

SELECT rt.id, rt.type, 
    rt.total_no_of_rooms, 
    if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms)) as "reserved rooms", 
    (rt.total_no_of_rooms - if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms))) as "rooms available"
FROM Roomtypes rt LEFT OUTER JOIN Reservations r 
    ON r.roomtype_id = rt.id 
    AND '2018-04-11 10:00:00' &gt;= r.check_in 
    AND '2018-04-12 09:59:59' &lt;= r.check_out 
GROUP BY rt.id, rt.type, rt.total_no_of_rooms


I have just added condition to check NULL value. If you are using PostgreSQL, use COALESCE function.