Return a ranking from tables with MySQL

Question

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

Possible duplicate of [How to get the rank of a row in mysql query](http://stackoverflow.com/questions/26928755/how-to-get-the-rank-of-a-row-in-mysql-query) — miken32, Feb 22 '17 at 17:59
Still struggling? See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 22 '17 at 18:09
@Strawberry, thanks for the tool: http://sqlfiddle.com/#!9/fe0a77 — F__M, Feb 22 '17 at 18:27

denny · Accepted Answer · 2017-02-23T02:55:51.943

1

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

edited Feb 23 '17 at 02:55

answered Feb 22 '17 at 19:31

denny

2,084
2
15
19

Ho wow that' perfect except the percentage value witch is 0.05 and not something like 50. Could you please help me with that please? – F__M Feb 22 '17 at 20:30
ok i updated.you need perchantage in form of that format than calculate with 10* – denny Feb 23 '17 at 02:56

Return a ranking from tables with MySQL

1 Answers1