Using aggregate functions (SUM, COUNT, AVG, MAX, MIN)

Aggregate functions are powerful tools in SQL that allow you to perform calculations on sets of rows and return a single result. These functions, such as SUM, COUNT, AVG, MAX, and MIN, provide valuable insights into data by summarizing and analyzing information. Here’s an overview of how to use aggregate functions in SQL.

Table of Contents

SUM

The SUM function calculates the sum of values in a column. It is typically used with numeric columns.

SELECT SUM(column_name)
FROM table_name;

To calculate the total sales amount from an “orders” table, you can use the SUM function:

SELECT SUM(sales_amount)
FROM orders;

This query will return the sum of all values in the “sales_amount” column.

COUNT

The COUNT function counts the number of rows in a table or the number of non-null values in a specific column.

-- Counts rows in a table
SELECT COUNT(*)
FROM table_name;

-- Counts rows in specific column
SELECT COUNT(column_name)
FROM table_name;

To count the number of orders in an “orders” table, you can use the COUNT function:

SELECT COUNT(*)
FROM orders;

This query will return the total number of rows in the “orders” table.

AVG

The AVG function calculates the average value of a numeric column. It disregards null values when calculating the average.

SELECT AVG(column_name)
FROM table_name;

To calculate the average salary of employees in an “employees” table, you can use the AVG function:

SELECT AVG(salary)
FROM employees;

This query will return the average salary across all employees.

MAX

The MAX function retrieves the maximum value from a column. It is commonly used with numeric or date/time columns.

SELECT MAX(column_name)
FROM table_name;

To find the highest sales amount from an “orders” table, you can use the MAX function:

SELECT MAX(sales_amount)
FROM orders;

This query will return the maximum value in the “sales_amount” column.

MIN

The MIN function retrieves the minimum value from a column. It is commonly used with numeric or date/time columns.

SELECT MIN(column_name)
FROM table_name;

To find the lowest salary among employees in an “employees” table, you can use the MIN function:

SELECT MIN(salary)
FROM employees;

It returns the minimum value in the “salary” column.

By using aggregate functions in SQL, you can perform calculations on sets of rows and retrieve valuable summary information. These functions allow you to derive meaningful insights from your data by performing operations such as summing values, counting rows, calculating averages, finding maximum and minimum values, and more.