Using Aggregate Functions to Summarize data.

Created Using DALL-E from OpenAI

Hello again! It’s time to talk about how we can use functions in SQL specifically functions that help us aggregate and organize our data whether by a specific column or condition. We learn how to calculate familiar descriptive statistics that we learned before in Excel such as the, Average,Sum,min,max and count. We will learn how to summarize larges amounts of data in a single line of code. What are you waiting for? Lets get started!

Aggregate Functions

Overview

1. The main aggregate functions are SUM, COUNT, AVG, MIN, and MAX.
2. You can combine aggregate functions for complex calculations. Such as subtracting the total of a something from the total of another column.
3. Aliasing is often used to rename columns for better readability. This is particularly useful when the aggregated columns have something like this as their column name “SUM(column_name)” etc.

SUM Function

Purpose: The SUM function adds up the values in a specified column depending on whether or not a filter is applied as well.

Basic Syntax:

SELECT SUM(column_name) FROM table;

Example with Filtering:

SELECT SUM(quantity)
FROM Order_details
WHERE productid = 10;

This query sums the quantity of a specific product (productid = 10) that was purchased.

Using the SUM Function in SQL ( Click to enlarge GIF )

COUNT Function

Purpose: The COUNT function returns the number of rows that match a specified condition or no condition if you haven't specified anything. Also it is the only one out of all these functions that i will talk about that can use the wildcard as its parameter.

Basic Syntax:

SELECT COUNT(column_name) FROM table;

Example with Filtering:

SELECT COUNT(*)
FROM Order_details
WHERE productid = 10;

This query counts the number of rows where the product ID is 10.

Using the COUNT Function in SQL ( Click to enlarge GIF )

AVG Function

Purpose: The AVG function returns the average value of a specified column. Also we can use conditions to filter it out for a specific average.

Basic Syntax:

SELECT AVG(column_name) FROM table;

Example with Filtering:

SELECT AVG(quantity)
FROM Order_details
WHERE productid = 10;

This query calculates the average quantity for product ID 10.

Using the AVG Function in SQL ( Click to enlarge GIF )

MIN Function

Purpose: The MIN function returns the lowest value in a specified column. Also can use conditions for specific things you might want.

Basic Syntax:

SELECT MIN(column_name) FROM table;

Example with Filtering:

SELECT MIN(quantity)
FROM Order_details
WHERE productid = 10;

This query finds the smallest quantity purchased for product ID 10.

Using the MIN Function in SQL ( Click to enlarge GIF )

MAX Function

Purpose: The MAX function returns the highest value in a specified column. Also you can use conditions as seen below to get the max a specific item etc.

Basic Syntax:

SELECT MAX(column_name) FROM table;

Example with Filtering:

SELECT MAX(quantity)
FROM Order_details
WHERE productid = 10;

This query finds the largest quantity purchased for product ID 10.

Using the MAX Function in SQL ( Click to enlarge GIF )

Overall, we can use aggregate functions to either add up data based on a criteria , average it , find the amount of data based on a particular column or condition and even find the max/min for your specified value as well. Later on we will discuss how to use GROUP BY in conjunction with these functions and thats where things are going to really take off.

For now make sure you have a good handle on how these functions generally work. Practice always makes perfect. Until next time have a Good morning, Good afternoon and Good night.