Mysql – Return a ranking from multiple table with theSQL

MySQL

Here my table structure:

___Segmentations:

|--------|------------|
| SEG_Id | SEG_Text   |
|--------|------------|
| 1      | Direct     |
| 2      | Website    |
| 3      | Other      |
|--------|------------|

___Bookings:

|--------|------------------|
| BOO_Id | BOO_Segmentation |
|--------|------------------|
| 1      | 1                |
| 2      | 1                |
| 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-25 | Night      |
| 6      | 2             | 2017-02-26 | Night      |
| 7      | 3             | 2017-02-28 | Night      |
| 8      | 3             | 2017-03-01 | Night      |
| 9      | 3             | 2017-03-02 | Night      |
| 10     | 3             | 2017-03-03 | Night      |
|--------|---------------|------------|------------|

I would like to know the most popular segmentation for a range of date.

The desired result should be this one for the following date range :

Form 2017-02-01 to 2017-02-28 inclusive

|------------|------------|------------|--------------|------------|
| ROO_Name   | Night_Nb   | Percentage | Booking_Nb   | Percentage |
|------------|------------|------------|--------------|------------|
| Direct     | 6          | 85.71      | 2            | 66.66      |
| Website    | 1          | 14.28      | 1            | 33.33      |
| Other      | 0          | 0          | 0            | 0          |
|------------|------------|------------|--------------|------------|

What I already tried:

SELECT r.SEG_Id
     , Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) Night_Nb
     , Concat(
         Format(
           Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) 
           / TotalBookings 
           * 100
         , 0) ) AS PercentageTotal
  FROM (  ___Segmentations r LEFT JOIN ___Bookings b ON r.SEG_Id = b.BOO_Segmentation
       ) INNER JOIN (SELECT BOO_HotelId
                          , Count(*) AS TotalBookings
                       FROM ___Bookings 
                      GROUP BY BOO_HotelId
                    ) AS TotalHotelBookings 
                 ON r.SEG_HotelId = TotalHotelBookings.BOO_HotelId
 WHERE r.SEG_HotelId = :hotel_id
 GROUP BY r.SEG_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/1aa10a

Best Answer

select seg.seg_text,
       ifnull(nights.Night_Nb,0) as Night_Nb,
       ifnull(nights.Percentage,0) as Percentage,
       ifnull(bookings.Booking_Nb,0) as Booking_Nb,
       ifnull(bookings.Percentage,0) as Percentage
  from __Segmentations seg
 left join ( select seg.seg_id,
                    count(*) as Night_Nb,
                    round(100*count(*)/(select count(*) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as nights  on (nights.seg_id=seg.seg_id)
 left join ( select seg.seg_id,
                    count(distinct bil.bil_bookingid) as Booking_Nb,
                    round(100*count(distinct bil.bil_bookingid)/(select count(distinct bil_bookingid) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as bookings  on (bookings.seg_id=seg.seg_id)
 group by seg.seg_id;
+----------+----------+------------+------------+------------+
| seg_text | Night_Nb | Percentage | Booking_Nb | Percentage |
+----------+----------+------------+------------+------------+
| Direct   |        6 |      85.71 |          2 |      66.67 |
| Website  |        1 |      14.29 |          1 |      33.33 |
| Other    |        0 |       0.00 |          0 |       0.00 |
+----------+----------+------------+------------+------------+
3 rows in set (0.02 sec)
Related Question