SELECT nom, SUM (quantite*tarif) as ca
FROM hotel H, consommation C, consommation_par_sejour CS,sejour S, consommation_par_hotel CH
WHERE H.id=S.hotel_id
AND S.id=CS.sejour_id
AND C.id=CS.consommation_id
AND H.id=CH.hotel_id
AND C.id=CH.consommation_id
GROUP BY nom 
ORDER BY ca desc