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.

  • COUNT
  • SUM
  • AVG
  • DISTINCT
  • MIN/MAX

COUNT

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:

SUM

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:

AVG

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

Output:

DISTINCT

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.

MIN/MAX

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.

What’s Next?

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.