A Dive into SQL Aggregate Functions

This article will explain what aggregate functions are and how to use them.

What is an Aggregate Functions

They are functions that operate on a set of rows to calculate and return a single value, we see the aggregate functions here:

1. AVG(): Returns a column’s average value.
2. COUNT(): Returns the number of rows in a column.
3. MAX(): Returns a column’s highest value.
4. MIN(): Returns a column’s lowest value.
5. SUM(): Returns the sum of a column’s values.

Using Aggregate Functions

SQL provides special functions for summarizing data without retrieving the entire set. This is a common requirement. Using these functions, SQL queries are frequently used to retrieve data for reporting and analysis.

Note: All the aggregate functions can be used to perform calculations on multiple columns using the standard mathematical operators, example: AVG(prod_price*quantity).

The AVG() Function

AVG() This function returns the average value of a column by calculating the number of rows and the sum of their values. AVG() can be used to return the average value of specific columns or rows.

Note: Column rows containing NULL values are ignored by the AVG() function.

Example:

The COUNT() Function

COUNT()essentially, it counts. You can determine the number of rows in a table or the number of rows that meet a specific criterion using the COUNT() function.

COUNT() can be used in 2 ways:

1. Use COUNT(*) to count the number of rows in a table, whether columns contain values or NULL values.
2. Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULL values.

Note: Column rows containing NULL values are ignored by the COUNT() function, but not if the asterisk (*) is used.

Example:

Example:

The MAX() Function

MAX() returns the highest value in the column specified. The MAX() function requires the column name to be specified.

Note: MAX() is typically used to find the highest numeric or date values, but many DBMSs (but not all) permit it to return the highest value in any column, including textual columns. MAX() returns the row that would be the last if the textual data were sorted by the specified column.

Note: Column rows with NULL values in them are ignored by the MAX() function.

Example:

The MIN() Function

MIN() does the exact opposite of MAX()—it returns the lowest value in a specified column. Like MAX(), MIN() requires that the column name be specified.

Note: Many (but not all) DBMSs permit the MIN() function to return the lowest value in any column, including textual columns. MIN() returns the row that would come first if the textual data were sorted by that column.

Note: Column rows with NULL values in them are ignored by the MIN() function.

Example:

The SUM() Function

SUM() is used to return the sum (total) of the values in a specific column.

Note: Column rows with NULL values in them are ignored by the SUM() function.

Example:

Aggregates on Distinct Values

The five aggregate functions can all be used in two ways:

1. To perform calculations on all rows, specify the ALL argument or specify no argument at all (because ALL is the default behavior), but No DISTINCT with COUNT(*), DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*). Similarly, DISTINCT must be used with a column name and not with a calculation or expression.
2. To include only unique values, specify the DISTINCT argument.

Note: In addition to the DISTINCT and ALL arguments shown here, some DBMSs also support the TOP and TOP PERCENT arguments, which allow you to perform calculations on subsets of query results. Consult the documentation for your DBMS to determine which arguments are available.

Example:

Combining Aggregate Functions

Example:

Summary

Today we learnt about aggregate functions and saw how easy they are, to extend your knowledge furthermore I would encourage you to check the group by.

I hope you enjoyed the article!