Make a report from 3 tables I need to make a report of 3 differences tables.

I tried something but I have an error.

Here my structure:
http://sqlfiddle.com/#!9/7c0e3a



I need something like this:

|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|
| RAT_Id | RAT_Name      | RAT_Price | booking_nb | booking_total | booking_pct | night_nb | night_total | night_pct |
|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|
| 1      | April Special | 125.00    | 2          | 1             | 50          | 3        | 4           | 75        |
|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|




Here what I tried but I have an error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':hotel_id ORDER BY t.RAT_Id ) q LEFT JOIN ( SELECT r.RAT_Id , COUNT(' at line 17:

SELECT q.RAT_Id
, q.RAT_Name
, SUM(IF(c.RAT_Id=q.RAT_Id,c.booking_nb,0))     AS booking_nb
, SUM(c.booking_nb)                             AS booking_total
, SUM(IF(c.RAT_Id=q.RAT_Id,c.booking_nb,0))
    * 100.0 / SUM(c.booking_nb)                 AS booking_pct
, SUM(IF(c.RAT_Id=q.RAT_Id,c.night_nb,0))       AS night_nb
, SUM(c.night_nb)                               AS night_total
, SUM(IF(c.RAT_Id=q.RAT_Id,c.night_nb,0))
* 100.0 / SUM(c.night_nb)                       AS night_pct
FROM ( SELECT t.RAT_Id
    , t.RAT_Name
    FROM ___Rates t
    WHERE t.RAT_HotelId = 'cus_AjGG401e9a840D'
    ORDER BY t.RAT_Id
) q
LEFT
JOIN (
    SELECT r.RAT_Id
    , COUNT(DISTINCT b.BOO_Id) AS booking_nb
    , COUNT(DISTINCT d.BIL_Id) AS night_nb
    FROM ___Rates r
    JOIN ___Bookings b
    ON d.BIL_RateId = r.RAT_Id
    JOIN `___BillableDatas` d
    ON d.BIL_BookingId = b.BOO_Id
    AND d.BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
    AND d.BIL_Type="Night"
    WHERE r.RAT_HotelId = 'cus_AjGG401e9a840D'
    GROUP
    BY r.RAT_Id
) c
ON 1=1   
GROUP
BY q.RAT_Id
, q.RAT_Name
ORDER
BY night_nb DESC, q.RAT_Name ASC
First: as noted by joanolo, the code you posted (when added to the set-up in your SQLFiddle link) generates a completely different error.

I obviously cannot guarantee that my changes are correct, but it appears that you were having problems with your JOIN clauses. The ON conditions for the JOINs weren't lined up correctly.

I've modified them as follows:

    FROM `___BillableDatas` d
    JOIN ___Bookings b
    ON d.BIL_BookingId = b.BOO_Id
    JOIN ___Rates r
    ON d.BIL_RateId = r.RAT_Id
    WHERE r.RAT_HotelId = 'cus_AjGG401e9a840D'
    AND d.BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
    AND d.BIL_Type="Night"


Note: I moved the d.BIL_Date and d.BIL_Type checks to the WHERE clause. It's not necessarily going to generate incorrect results to put them in the JOIN, but I generally recommend that only the checks that are explicitly tied to how the two tables' rows relate to each other be included in the ON clause; it helps clarify your intentions, and makes changing the query later easier.

And here's a SQLFiddle link with the modified code included. It returns a single row.

Now: based on the error you report, I don't think it's necessarily a MySQL issue. It seems likely that you're running the SQL command from somewhere else (PHP code, for instance), and that you've got a problem with how you're passing in your parameters to the statement. I've seen :hotel_id used in such code to indicate a parameter to be replaced, and the fact that you need to use the same value at two points in the code may be causing your problem. It's a direction to look, at least.

Hope this helps.