1 2 3 4 5 6 7 8 9 10 11
| SELECT CASE WHEN LOWER(estab.ESTABLISHMENT_CITY) IN ('münchen', 'munchen', 'munich') THEN 'munich'
WHEN LOWER(estab.ESTABLISHMENT_CITY) IN ('cologne', 'köln','koln','koeln') THEN 'cologne'
ELSE LOWER(estab.ESTABLISHMENT_CITY) END AS ESTABLISHMENT_CITY_AGG,
COUNT(DISTINCT estab.ESTABLISHMENT_CODE) AS Nb_hotels
FROM DIM_ESTABLISHMENT AS estab
WHERE LOWER(estab.ESTABLISHMENT_CITY) IN ('adelaide', 'amsterdam', 'atlanta', 'auckland', 'austin', 'barcelona', 'berlin', 'birmingham', 'bordeaux', 'boston', 'brisbane', 'bristol', 'brussels', 'canberra', 'chicago', 'cologne', 'dallas', 'dubai', 'dublin', 'edinburgh', 'florence', 'frankfurt', 'geneva', 'glasgow', 'gold coast', 'hamburg', 'hobart', 'lille', 'lisbon', 'liverpool', 'london', 'los angeles', 'lyon', 'madrid', 'manchester', 'marseille', 'melbourne', 'mexico city', 'miami', 'milan', 'montreal', 'munich', 'naples', 'nantes', 'new york', 'nice', 'paris', 'perth', 'porto', 'prague', 'quebec', 'rio de janeiro', 'rome', 'san francisco', 'sao paulo', 'seattle', 'seville', 'singapore', 'stuttgart', 'sydney', 'toronto', 'toulouse', 'valencia', 'vancouver', 'vienna', 'warsaw', 'washington', 'zurich')
GROUP BY CASE
WHEN LOWER(estab.ESTABLISHMENT_CITY) IN ('münchen', 'munchen', 'munich') THEN 'munich'
WHEN LOWER(estab.ESTABLISHMENT_CITY) IN ('cologne', 'köln','koln','koeln') THEN 'cologne'
ELSE LOWER(estab.ESTABLISHMENT_CITY) END
ORDER BY Nb_hotels DESC; |
Partager