Mastering SQL Aggregate Functions: SUM, MAX, MIN, AVG

In this article, we will discuss some aggregate functions in SQL. These functions are used to perform calculations on specific sets of records in a table.

For the examples in this article, we will use the following table as a reference.

To create this table, we used the following commands.

CREATE TABLE SALES(ID_SALES INT
                  ,ID_PRODUCET INT
                  ,PRODUCT_NAME VARCHAR(255)
                  ,PRODUCT_PRICE DECIMAL(10, 2)
                  ,SALE_DATE DATE
                  ,SALE_COUNT INT)

INSERT INTO SALES (ID_SALE
                  ,ID_PRODUCT
                  ,PRODUCT_NAME
                  ,PRODUCT_PRICE
                  ,SALE_DATE
                  ,SALE_COUNT)
VALUES
(1, 100, 'Boots', 79.99, '2023–10–01', 5),
(2, 101, 'T-shirt', 19.99, '2023–10–01', 3),
(3, 300, 'Sneakers', 49.99, '2023–10–01', 2),
(4, 400, 'Bike', 349.99, '2023–10–03', 1),
(5, 500, 'Boxing Gloves', 29.99, '2023–10–04', 2),
(6, 600, 'Basketball Shirt', 24.99, '2023–10–04', 6),
(7, 402, 'Surfboard', 149.99, '2023–10–05', 1),
(8, 301, 'Racket', 39.99, '2023–10–05', 3),
(9, 900, 'Watch', 69.99, '2023–10–05', 4),
(10, 401, 'Skate', 59.99, '2023–10–06', 2 );

SUM

The SUM function returns the sum of a numerical column in the specified set of records in the table.

The basic syntax of this function is as follows.

SELECT SUM(COLUMN)
  FROM TABLE

Let's see a practical example with our sample table. In this example, we are calculating the total quantity of sales made in the store.

SELECT SUM(SALE_COUNT) AS SUM_SALE_COUNT
  FROM SALE

The result we obtain is:

29

The SUM function is often used in combination with the GROUP BY clause to calculate sums in distinct groups of records, allowing you to analyze aggregate data based on specific criteria. For example, if we want to see the quantity of items sold on each day, we use the following syntax.

SELECT SALE_DATE, SUM(SALE_COUNT) AS SUM_SALE_COUNT
  FROM SALE
GROUP BY SALE_DATE

The result is:

MAX

The MAX function returns the maximum value in a specific column of a table. It allows you to obtain the highest value present in a data column, which can be useful in various situations, such as finding the highest price in a price list, the highest score in a set of results, and more.

The basic syntax of this function is as follows.

SELECT MAX(COLUMN)
  FROM TABLE

A practical example of using the MAX function, based on our sample table, is when we want to know which product is the most expensive.

SELECT MAX(PRODUCT_PRICE) AS MAX_PRODUCT_PRICE
  FROM SALE

The result we obtain is as follows:

349.99

The MAX function is especially useful when you want to identify the highest value in a dataset or when you need to find the record with the maximum value in a specific column. Additionally, it can be combined with other SQL clauses, such as WHERE and GROUP BY, to obtain more refined or filtered results based on specific criteria.

For example, we may want to retrieve the best-selling product on a specific day. For this, we use the following script.

SELECT TOP 1 PRODUCT_NAME, MAX(SALE_COUNT) AS MAX(SALE_COUNT)
  FROM SALE
 WHERE SALE_DATE = '2023–10–01'
GROUP BY PRODUCT_NAME

The result being:

MIN

The MIN function works similarly to the MAX function, but it returns the lowest value present in a data column.

The basic syntax of this function is as follows.

SELECT MIN(COLUMN)
  FROM TABLE

A practical example of using the MIN function, based on our sample table, is when we want to know which product is the least expensive.

SELECT MIN(PRODUCT_PRICE) AS MIN_PRODUCT_PRICE
  FROM SALE

And this is the result we obtain.

19.99

Just like in the MAX function, we can combine the MIN function with other SQL clauses.

For example, we may want to retrieve the least sold product on a specific day. For this, we use the following script.

SELECT TOP 1 PRODCUT_NAME, MIN(SALE_COUNT) AS MIN_SALE_COUNT
  FROM SALE
 WHERE SALE_DATE = '2023–10–01'
GROUP BY PRODUCT_NAME
ORDER BY MIN(SALE_COUNT)

With the following result:

AVG

The AVG function is used to calculate the average of values in a specific numerical column, which is useful in situations where you want to find the average of data, such as calculating price averages, scores, ratings, and more.

The basic syntax of the AVG function is as follows.

SELECT AVG(COLUMN)
  FROM TABLE

Using our sample table, let's look at a scenario where we want to find the average price of products sold.

SELECT AVG(PRODUCT_PRICE * SALE_COUNT) AVG_PRICE
  FROM SALE

The result is as follows.

178.971

The AVG function is often used in combination with the GROUP BY clause to calculate averages in distinct groups of records and the WHERE clause to filter data, allowing you to analyze aggregate data based on specific criteria. It is useful for calculating average numerical values in large datasets and is a valuable tool in statistical analysis.

Let's see an example where we want to know the average selling price per day.

SELECT SALE_DATE, AVG(PRODUCT_PRICE * SALE_COUNT) AS AVG_PRICE
  FROM SALE
GROUP BY SALE_DATE

The result we obtain is as follows.

In conclusion, the aggregation functions MIN, MAX, SUM, and AVG in SQL are fundamental features for performing calculations on datasets and obtaining valuable insights from your stored data in databases. Each of these functions plays a distinct role in data analysis and generating relevant statistics.

Mastering the use of these functions is essential for professionals working with databases, software development, or data analysis and can be a crucial skill in interpreting information and conducting meaningful analyses in an increasingly data-driven world. Therefore, exploring these functions and deepening your SQL knowledge is an important step for those looking to unlock the full potential of their data.