CODEWITHSUNDEEP
sqloracle-database
bluewave41
bluewave41

Reputation: 145

List total and conditional counts while joining multiple tables

I've been given the following tables:

Hotel (hotelNo, hotelName,hotelAddress)   
Room (roomNo,hotelNo, type, price)
Guest (guestNo, guestName, guestAddress)
Booking (hotelNo,guestNo,dateFrom, dateTo, roomNo)

The question asks: "What is the percentage of hotels still under construction? List the total number of hotels, number of hotels completed, the number of hotels under construction, and the percentage of hotels under construction."

For the total number of hotels I know I can do this:

SELECT COUNT(Hotel.hotelNo)
FROM HOTEL;

2 hotels are under construction which I know I can check for using:

SELECT COUNT(Hotel.hotelNo)
FROM HOTEL
INNER JOIN ROOM
ON ROOM.hotelNo = Hotel.hotelNo
HAVING COUNT(roomNo) = 0;

And then hotels that are completed:

SELECT COUNT(Hotel.hotelNo)
FROM HOTEL
INNER JOIN ROOM
ON ROOM.hotelNo = Hotel.hotelNo
HAVING COUNT(roomNo) > 0;

I don't know how I would go about putting all of these into one query though. I tried something like this:

SELECT COUNT(Hotel.hotelNo),
(SELECT COUNT(Hotel.hotelNo)
FROM HOTEL
LEFT JOIN ROOM
ON ROOM.hotelNo = Hotel.hotelNo
GROUP BY Room.roomNo
HAVING COUNT(roomNo) = 0) as count
FROM HOTEL;

That requires though that I have a group by statement for Hotel.hotelNo but when added just gives me a table of 10 1's for COUNT(Hotel.hotelNo) because there are 10 hotels and then 10 2's because 2 hotels are under construction for all those entries.

I want it to look something like this:

TOTAL COMPLETED CONSTRUCTION PERCENTAGE
---------------------------------------
   10         8            2         20

How would I go about this?

Upvotes: 1

Views: 218

Answers (2)

David Faber
David Faber

Reputation: 12485

I would be surprised if your query to get hotels under construction,

SELECT COUNT(Hotel.hotelNo)
FROM HOTEL
INNER JOIN ROOM
ON ROOM.hotelNo = Hotel.hotelNo
HAVING COUNT(roomNo) = 0;

actually worked. The reason I say this is it looks like a query that ought to be a left join, looking for values of hotelNo for which there are no corresponding rows in ROOM.

Here is how you might put the two queries together:

SELECT COUNT(*) AS total
     , SUM(DECODE(room_cnt, 0, 0, 1)) AS completed
     , SUM(DECODE(room_cnt, 0, 1, 0)) AS construction
     , SUM(DECODE(room_cnt, 0, 1, 0))/COUNT(*) AS percentage
  FROM (
    SELECT h.hotelNo, COUNT(r.roomNo) AS room_cnt
      FROM hotel h LEFT JOIN room r
     WHERE h.hotelNo = r.hotelNo
     GROUP BY h.hotelNo
);

You can use CASE instead of DECODE() if you are fond of ANSI SQL.

Upvotes: 0

potashin
potashin

Reputation: 44601

You can try something like this :

SELECT tmp.*
     , (tmp.total-tmp.complete) AS construction
     , ((1-tmp.complete/tmp.total)*100) AS percentage
FROM ( SELECT COUNT(DISTINCT h.hotelNo) AS total
            , COUNT(DISTINCT r.hotelNo) AS complete
       FROM HOTEL h 
       LEFT JOIN ROOM r ON r.hotelNo = h.hotelNo ) tmp

SQLFiddle

Upvotes: 1

Related Questions