CODEWITHSUNDEEP
mysql
F__M
F__M

Reputation: 1598

Return a ranking from tables with 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.

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

Upvotes: 1

Views: 73

Answers (1)

denny
denny

Reputation: 2274

try this

select Roo_Name,coalesce(bookid,0) as nightdb,coalesce(bookid * 10/Boo_Id,0) as percentage
from ___Rooms r1 
left join 
(select count(BOO_RoomId) as book, BOO_Id 
 from ___Bookings group by  BOO_Id) b1 
on r1.Roo_Id = b1.Boo_id 
left join 
(select count(Bil_BookingId) as bookid,BIL_BookingId 
from ___BillableDatas  
group by BIL_BookingId) b2 
on b2.BIL_BookingId = b1.BOO_Id group by r1.Roo_Name;

DEMO

Upvotes: 1

Related Questions