Aggregate Function

Where and Having

Demo Practice

  1. How many in total Airbnb in Brooklyn neighborhood according to their review in 2018?

    The total Airbnb in Brooklyn is 11

    SELECT SUM(name) AS total_airbnb_ny FROM airbnb_ny WHERE neighbourhood_group = "Brooklyn";

    Untitled

    Where we actually want to know how many according to their review, so we can filter by adding “AND” what kind of column = years

    SELECT SUM(name) AS total_airbnb_ny FROM airbnb_ny WHERE neighbourhood_group = "Brooklyn" AND last_review = 2018;

    The result finally shows 6 airbnb

    Untitled

  2. How is the trend of Airbnb in Manhattan neighborhood according to their review from 2017 to 2019?

    SELECT SUM(name) FROM airbnb_ny AS total_airbnb_in_Manhattan WHERE neighbourhood_group = "Manhattan" AND last_review >2017 AND last_review <=2019;

    The result will be

    Untitled

  3. How many the total Airbnb each of neighborhoods in New York in 2019

    SELECT room_type, neighbourhood_group, COUNT(name) AS total_numbers_airbnb 
FROM airbnb_ny WHERE room_type = "private room" GROUP BY neighbourhood_group;

    The total will be

    Untitled

  4. We want to know the minimum value and maximum from column the price

    SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price
FROM airbnb_ny;

    Untitled

  5. Which neighborhoods that has Airbnb lower than 100?

    SELECT neighbourhood_group, SUM(name) 
AS total_number_airbnb_by_neighbourhoods
FROM airbnb_ny GROUP BY neighbourhood_group 
HAVING total_number_airbnb_by_neighbourhoods < 100;

    The result will be

    Untitled