F. Aggregate Functions

An aggregate function is used to combine a set of values and return a single result.

1. COUNT

We want to find out the number of Airbnb listings we have. To do so we use the COUNT() function which will add up the number of rows where the specified column is not NULL.

1SELECT COUNT()
2FROM airbnb_listings;

2. SUM

Let’s say we want to find the total number_of_reviews left on our listings. Using the SUM function we can return the total sum of a numeric column.

1SELECT SUM(number_of_reviews) 
2FROM airbnb_listings; 

3. AVERAGE

Let’s say you want to see what the average price is across your listings is. By using the AVG function the average value of the numeric column will be returned.

1SELECT AVG(price) 
2FROM airbnb_listings;

4. MAXIMUM & MINIMUM

To find what is the maximum and minimum price of our listings we use the MIN and MAX functions.

1 --  Max
2SELECT MAX(price) 
3FROM airbnb_listings;
4
5 --  Min
6SELECT MIN(price) 
7FROM airbnb_listings;

5. GROUP BY

Let’s say we want to find the average price for each neighborhood. We can do this using the GROUP BY statement. It groups rows with the same values into summary rows and is commonly used with aggregate functions like AVG.

1SELECT neighbourhood, AVG(price)
2FROM airbnb_listings
3GROUP BY neighbourhood;

6. HAVING

HAVING acts in the same way as the WHERE clause with the difference being that HAVING is used for aggregate functions. Let’s suppose want to return the names of the clients who have more than 2 listings on Airbnb.

1SELECT COUNT(host_id), name
2FROM airbnb_listings
3GROUP BY host_name
4HAVING COUNT(host_id) > 2;

7. ORDER BY

To order the results obtained from a query we use the ORDER BY command. By default the order will be ascending (A -> Z / 1 -> 100) however we can change this using DESC. Let’s say for example we want to order our listings by price.

1SELECT name, price
2FROM airbnb_listings
3ORDER BY price;
4
5 -- In descending order
6SELECT name, price
7FROM airbnb_listings
8ORDER BY price DESC;