help with SQL
Posted on 2015-01-09
I have two tables; a house table and a bed table. The house table has a long value that is the number of total beds in the house. The bed table has record for each bed that included a description, type, etc., and a status that indicates if the bed is vacant or occupied. What I'm trying to do in one query is return all houses that have at least 1 open bed, with columns for total beds, occupied beds and vacant beds. I don't know how to combine the JOIN with one or more aggregate functions that give a count of bed records with a status of vacant and/or occupied, or one aggregate function that give the number of vacant beds along with a calculation of: occupied bed = total beds - vacant beds.