Up till now, we've been focussed on filtering rows, and ordering rows. Now, it's time to show columns some love in this lesson by covering aggregate functions, which perform operations across an entire column of data.

Here's the 5 SQL Aggregate Functions β memorize them by heart:

- adds together all the values in a particular column.
- returns the lowest value in a particular column
- returns the highest value in a particular column
- calculates the average of a group of selected values.
- counts how many rows are in a particular column.

Think of aggregate functions as your data detectives π΅π»ββοΈ, helping you extract precious insights from your database, whether you're counting customers, summing up sales, or analyzing real estate property trends. By the end of this tutorial, you'll realize how SQL is good at deriving insights β not just filtering rows!

To bring these concepts to life, we'll be working with the dataset which contains data on what products Amazon customers are buying, and how much they are buying them for.

**Table: Sample Data**

category | product | user_id | spend | transaction_date |
---|---|---|---|---|

appliance | washing machine | 123 | 219.80 | 03/02/2022 11:00:00 |

electronics | vacuum | 178 | 152.00 | 04/05/2022 10:00:00 |

electronics | wireless headset | 156 | 249.90 | 07/08/2022 10:00:00 |

electronics | vacuum | 145 | 189.00 | 07/15/2022 10:00:00 |

electronics | computer mouse | 195 | 45.00 | 07/01/2022 11:00:00 |

appliance | refrigerator | 165 | 246.00 | 12/26/2021 12:00:00 |

appliance | refrigerator | 123 | 299.99 | 03/02/2022 11:00:00 |

... | ... | ... | ... | ... |

Let's dive into some practical examples of using aggregate functions to analyze this Amazon data.

The function enables you to count the total number of rows in a table. We can use the function on the column as follows:

Here's an example query:

**Results**

count |
---|

15 |

Frequently, one of the first things we do after 'ing the data to explore it is then count how big the dataset is. We do this with the following query:

Give a try by writing a query to see how big the CVS pharmacy sales dataset is.

The function calculates the sum of numeric values in a column. To calculate the total amount of money spent on Amazon products, we can use the function to sum the column.

**Results**

sum |
---|

2142.76 |

Practice by finding the total sales of all Pfizer manufactured medicines sold at CVS Pharmacy.

The function computes the average value of numeric data in a column. To find the average price of an Amazon order, we can use the function on the column.

**Results**

avg |
---|

142.8506666666666667 |

Practice in the exercise below, by finding the average stock open price for Google Stock.

The function identifies the smallest value in a column. To find the lowest priced Amazon product order, we can use the function on the column.

**Results**

min |
---|

7.99 |

Use SQL's MIN command in the practice exercise below, to find the lowest price Microsoft stock ever opened at.

The function retrieves the largest value in a column. To find the highest priced Amazon product order, we can use the function on the column.

**Results**

max |
---|

299.99 |

In the practice exercise below, use the aggregate function to find the highest price Netflix stock ever opened at.

**1. Calculating Total Sales for Financial Analysis π©π»βπ»**

**Scenario:**A business wants to analyze its total sales over a specific period to assess its financial performance.**Use Case:**The function can be used to calculate the total revenue generated from sales during the chosen time frame, aiding in financial planning and analysis.

**2. Average Ratings for Product Reviews ποΈ**

**Scenario:**An e-commerce platform wants to showcase the average rating of products based on customer reviews.**Use Case:**The function can calculate the average rating of products, helping customers make informed purchasing decisions.

**3. Identifying Most Active Usersπ±**

**Scenario:**A social networking site wants to recognize its most active users based on the number of posts they've made.**Use Case:**The function can tally the number of posts for each user, highlighting the most prolific contributors.

**4. Finding Lowest and Highest Prices βοΈ**

**Scenario:**A travel website wants to display the cheapest and most expensive flight options for users.**Use Case:**The and functions can identify the lowest and highest prices for flights, allowing users to quickly compare options.

**5. Tracking Student Performance in Education π**

**Scenario:**An educational institution wants to track student performance by calculating average scores and identifying top performers.**Use Case:**The function can compute the average scores of students, while the function can help find the highest scorers.

To take your analysis to the next level, we won't just use aggregate functions like SUM or AVG on an entire dataset β we'll analyze sub-groups of data using the GROUP BY command!

Next Lesson

SQL GROUP BY π