SELECT
        `offers`.`ID`,
        `offers`.`UploadID`,
        `offers`.`Image`,
        `offers`.`Size`,
        `offers`.`TripType`,
        `offers`.`Currency`,
        `offers`.`Title`,
        `offers`.`URLLabel`,
        `offers`.`Annotation`,
        `offers`.`Visible`,
        `programs`.`StartFrom`,
        `programs`.`EndTo`,
        `programs`.`Price`,
        `programs`.`PromoPrice`,
        `programs`.`PromoDate`,
        COUNT(`rooms`.`ID`) AS `RoomsNum`,
        COUNT(`reservation_rooms`.`RoomID`) AS `BookedNum`
    FROM
        `offers`
        INNER JOIN `programs` ON
            `offers`.`ID` = `programs`.`OfferID` AND
            `offers`.`Language` = `programs`.`Language`
    WHERE
        `offers`.`ParentID` = 67 AND
        `offers`.`Language` = 'bg' AND
        `programs`.`EndTo` >= CURDATE() AND
        `offers`.`Visible` = 1
 GROUP BY `offers`.`ID` ORDER BY `offers`.`Row` ASC, `offers`.`PostDate` ASC


-----------------------------------

SELECT
    `offers`.`ID`,
    `offers`.`Currency`,
    `offers`.`Country`,
    `offers`.`Title`,
    `offers`.`URLLabel`,
    MONTH(`programs`.`StartFrom`) AS `Month`,
    `programs`.`StartFrom`,
    `programs`.`EndTo`,
    `programs`.`Price`,
    `programs`.`PromoPrice`,
    `programs`.`PromoDate`,
    SUM(`rooms`.`Beds`) AS `BedsNum`,
    COUNT(`rooms`.`ID`) AS `RoomsNum`,
    SUM(`R2`.`Beds`) AS `BookedBedsNum`,
    COUNT(`reservation_rooms`.`RoomID`) AS `BookedRoomsNum`
FROM
    `offers`
    INNER JOIN `programs` ON
        `offers`.`ID` = `programs`.`OfferID` AND
        `offers`.`Language` = `programs`.`Language`
    LEFT JOIN `rooms` ON
        `programs`.`ID` = `rooms`.`ProgramID` AND
        `rooms`.`Language` = `offers`.`Language` AND
        `rooms`.`Visible` = 1
    LEFT OUTER JOIN `reservation_rooms` ON `reservation_rooms`.`RoomID` = `rooms`.`ID`
    LEFT OUTER JOIN `rooms` AS `R2` ON `reservation_rooms`.`RoomID` = `R2`.`ID`
WHERE
    `offers`.`ParentID` = 67 AND
    `offers`.`Language` = 'bg' AND
    `offers`.`URLLabel` != 'shred-together' AND
    `programs`.`EndTo` >= CURDATE() AND
    `offers`.`Visible` = 1
GROUP BY
    `offers`.`ID`
ORDER BY
    `programs`.`StartFrom` ASC,
    `offers`.`PostDate` ASC


 -----------------------------------

SELECT
    `offers`.`ID`,
    `offers`.`Currency`,
    `offers`.`Country`,
    `offers`.`Title`,
    `offers`.`URLLabel`,
    MONTH(`programs`.`StartFrom`) AS `Month`,
    `programs`.`StartFrom`,
    `programs`.`EndTo`,
    `programs`.`Price`,
    `programs`.`PromoPrice`,
    `programs`.`PromoDate`,
    SUM(`rooms`.`Beds`) AS `BedsAvl`,
    SUM(`R2`.`Beds`) AS `BedsRes`,
    (SUM(`rooms`.`Beds`) -  SUM(`R2`.`Beds`)) AS `Beds`
FROM
    `offers`
    INNER JOIN `programs` ON
        `offers`.`ID` = `programs`.`OfferID` AND
        `offers`.`Language` = `programs`.`Language` AND
        `programs`.`Visible` = 1
    INNER JOIN `rooms` ON
        `programs`.`ID` = `rooms`.`ProgramID` AND
        `rooms`.`Language` = `offers`.`Language` AND
        `rooms`.`Visible` = 1
    LEFT JOIN `reservation_rooms` ON `reservation_rooms`.`RoomID` = `rooms`.`ID`
    LEFT JOIN `rooms` AS `R2` ON `reservation_rooms`.`RoomID` = `R2`.`ID` AND `R2`.`Visible` = 1
WHERE
    `offers`.`ParentID` = 67 AND
    `offers`.`Language` = 'bg' AND
    `offers`.`URLLabel` != 'shred-together' AND
    `offers`.`Visible` = 1 AND
    `programs`.`EndTo` >= CURDATE()
GROUP BY
    `programs`.`ID`
ORDER BY
    `programs`.`StartFrom` ASC,
    `offers`.`PostDate` ASC
