Mysql – Return a ranking from tables with MySQL

MySQL

Here my table structure:

___Rooms:

|--------|------------|
| ROO_Id | ROO_Name   |
|--------|------------|
| 1      | Room 1     |
| 2      | Room 2     |
| 3      | Room 3     |
|--------|------------|

___Bookings:

|--------|------------|
| BOO_Id | BOO_RoomId |
|--------|------------|
| 1      | 1          |
| 2      | 2          |
| 3      | 2          |
|--------|------------|

___BillableDatas:

|--------|---------------|------------|------------|
| BIL_Id | BIL_BookingId | BIL_Date   | BIL_Item   |
|--------|---------------|------------|------------|
| 1      | 1             | 2017-02-21 | Night      |
| 2      | 1             | 2017-02-22 | Night      |
| 3      | 1             | 2017-02-23 | Night      |
| 4      | 1             | 2017-02-24 | Night      |
| 5      | 2             | 2017-02-30 | Night      |
| 6      | 2             | 2017-02-31 | Night      |
| 7      | 1             | 2017-02-31 | Night      |
|--------|---------------|------------|------------|

I would like to know the most popular room.

The desired result should be:

|------------|------------|------------|
| ROO_Name   | Night Nb   | Percentage |
|------------|------------|------------|
| Room 1     | 5          | 71.42      |
| Room 2     | 2          | 28.57      |
| Room 3     | 0          | 0          |
|------------|------------|------------|

What I already tried:

SELECT r.ROO_Id
     , Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) NumBookings
     , Concat(
         Format(
           Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) 
           / TotalBookings 
           * 100
         , 0) ) AS PercentageTotal
  FROM (  ___Rooms r LEFT JOIN ___Bookings b ON r.ROO_Id = b.BOO_RoomId
       ) INNER JOIN (SELECT BOO_HotelId
                          , Count(*) AS TotalBookings
                       FROM ___Bookings 
                      GROUP BY BOO_HotelId
                    ) AS TotalHotelBookings 
                 ON r.ROO_HotelId = TotalHotelBookings.BOO_HotelId
 WHERE r.ROO_HotelId = :hotel_id
 GROUP BY r.ROO_Id
 ORDER BY NumBookings DESC

But it doesn't work actually.

Could anyone help me with this please ?

You could use the SQL Fiddle:
http://sqlfiddle.com/#!9/fe0a77

Best Answer

I've changed the 'impossible dates' and I've set a rextester sample.

First group by RoomId and BookingId and calculate percentage.

(
 SELECT      b.BOO_RoomId, bd.BIL_BookingId, 
             COUNT(*) as NumNights,
             COUNT(*) * 100 / (SELECT COUNT(*) FROM ___BillableDatas) AS Percent 
 FROM        ___BillableDatas bd
 LEFT JOIN   ___Bookings B
 ON          b.BOO_Id = bd.BIL_BookingId
 GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
) co

Then LEFT JOIN with rooms table and show all values:

SELECT      r.ROO_Name,
            COALESCE(NumNights, 0) NumNights,
            COALESCE(Percent, 0) Percent
FROM        ___Rooms r
LEFT JOIN   (
            SELECT      b.BOO_RoomId, bd.BIL_BookingId, 
                        COUNT(*) as NumNights,
                        COUNT(*) * 100 / (SELECT COUNT(*) 
                                          FROM ___BillableDatas
                                          WHERE BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
                                          ) AS Percent 
            FROM        ___BillableDatas bd
            LEFT JOIN   ___Bookings B
            ON          b.BOO_Id = bd.BIL_BookingId
            WHERE       BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
            GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
            ) co
ON          R.ROO_Id = co.BOO_RoomId
;

+----------+---------------+-----------+---------+
| ROO_Name | BIL_BookingId | NumNights | Percent |
+----------+---------------+-----------+---------+
| Room 1   |       1       |     5     | 71,4286 |
| Room 2   |       2       |     2     | 28,5714 |
| Room 3   |       0       |     0     |  0,0000 |
+----------+---------------+-----------+---------+
Related Question