1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| SELECT
Resort.resort
, SUM(Invoice_Line.nb_guests)
FROM
Invoice_Line
INNER JOIN
Service
ON ( Invoice_Line.service_id=Service.service_id )
INNER JOIN
Service_Line,
ON ( Service.sl_id=Service_Line.sl_id )
INNER JOIN
Resort
ON ( Resort.resort_id=Service_Line.resort_id )
WHERE
( Service_Line.service_line = 'Accommodation' )
GROUP BY
Resort.resort
HAVING
SUM(Invoice_Line.nb_guests) >=
( SELECT MAX(tot_guests.sum_guests)
FROM
( SELECT Resort.resort
, SUM(Invoice_Line.nb_guests) AS sum_guests
FROM
Invoice_Line
INNER JOIN
Service
ON ( Invoice_Line.service_id=Service.service_id )
INNER JOIN
Service_Line
ON ( Service.sl_id=Service_Line.sl_id )
INNER JOIN
Resort
ON ( Resort.resort_id=Service_Line.resort_id )
WHERE
( Service_Line.service_line = 'Accommodation' )
GROUP BY
Resort.resort
) AS tot_guests
)
; |
Partager