How to use SQL AVG()

SQL AVG() calculates the average value of a column, ignoring NULL values. It can be combined with other operations as well.

What is SQL AVG()?

The SQL AVG() function is a part of the Structured Query Language and is used to specify the mean or average value of stored values in a specific column. The SQL command SELECT is used for the query. This function is particularly valuable for tasks like creating balance sheets, analyses and price calculations. It quickly and easily provides a reliable overview with average values. However, it is important to note that NULL values are not considered in the calculation.

Syntax and function

The basic syntax of the function is simple and contains only a few parameters:

SELECT AVG(name_of_column) 
FROM name_of_table;

In this case, you only initiate the output of the mean value via SELECT and the function. Use FROM to specify which table is to be taken into account.

To use the SQL AVG() function more precisely, you can use the WHERE condition:

SELECT AVG(name_of_column) 
FROM name_of_table 
WHERE precondition;

You can also list several prerequisites.

Examples of how SQL AVG() works

The best way to understand how the SQL AVG() function works is to use it for a specific purpose. Imagine a table called ‘CustomerList’ that stores customer details, including customer number, location, number of products purchased, and the total amount in pounds for a specific period. Here’s what the table looks like:

Now you can use the function to find out the average amount these customers have paid. The corresponding command is:

SELECT AVG(Sum)
FROM CustomerList

The result would look like this:

If instead you want to determine how many items customers have ordered on average, the principle is similar:

SELECT AVG(Product)
FROM CustomerList;

The result is:

The function in combination with WHERE

As mentioned earlier, you can add one or more conditions to the function to obtain more specific results. For example, to calculate the average turnover of all customers from Liverpool, use the following command:

SELECT AVG(Sum)
FROM CustomerList
WHERE Location = 'Liverpool';

This gives us the following result:

Combination with other parameters

You can also combine the function with other parameters. In the following example, we display all customers whose spending is above the average:

SELECT Customer number, Name, Location, Product, Sum
FROM CustomerList
WHERE Sum > (SELECT AVG(Sum) FROM CustomerList);

The display would be:

Other functions similar to SQL AVG()

In addition to SQL AVG(), there are other functions to easily calculate specific values. For example, to determine the sum of all numerical values in a column, use SUM(). To find out how many rows fulfill a certain requirement, use the COUNT() function.