Lesson 9 – Aggregate Functions
Aggregate functions the functions that operate on a group of rows to perform an aggregate operation on the data. For instance, if you want to calculate the total price of all of the products in the products table, you can use aggregate functions. In this lesson, you will see the five most commonly used aggregate functions.
The COUNT function is used to count the total number of rows. To count the rows, you have to specify SELECT then COUNT (*) followed by any custom header value and then FROM table. The following query returns the count of rows from the products table.
SELECT COUNT (*) as TOTAL_ROWS FROM products
Here the output:
The sum function is used to add values from multiple rows of a specific column. The following SUM function calculates the sum of prices for all the products.
SELECT SUM (price) as TOTAL_PRICE FROM products
Here is the output:
The average query finds the average value from multiple rows. The following query returns the average price.
SELECT AVG (price) as Average_Price FROM products
The DISTINCT function returns the unique values from a column. For instance, if you want to return the unique category ids, you can use DISTINCT as follows:
SELECT DISTINCT (category_id) as Unique_Categories FROM products
Here you can use the ORDER BY clause to sort the category ids.
The MIN/MAX functions are used to select the minimum or maximum value from multiple rows. For instance, the following query returns the minimum price from the products table.
SELECT MIN (price) as Minimum_Price FROM products
Here is the output:
In the same way, you can find the maximum value using MAX function.
In this lesson, you saw how to apply aggregate functions on multiple rows. But what if you want to apply aggregate functions on a subset of the data? For instance, what if you want to get the sum of the price of all the items in different categories? You will see the answer to this question in the next lesson.