How to use SQL AVG()

SQL AVG() cal­cu­lates the average value of a column, ignoring NULL values. It can be combined with other op­er­a­tions as well.

What is SQL AVG()?

The SQL AVG() function is a part of the Struc­tured 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 par­tic­u­larly valuable for tasks like creating balance sheets, analyses and price cal­cu­la­tions. It quickly and easily provides a reliable overview with average values. However, it is important to note that NULL values are not con­sidered in the cal­cu­la­tion.

Syntax and function

The basic syntax of the function is simple and contains only a few para­met­ers:

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 pre­requis­ites.

Examples of how SQL AVG() works

The best way to un­der­stand how the SQL AVG() function works is to use it for a specific purpose. Imagine a table called ‘Cus­tom­er­L­ist’ 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 cor­res­pond­ing 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 com­bin­a­tion with WHERE

As mentioned earlier, you can add one or more con­di­tions 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:

Com­bin­a­tion with other para­met­ers

You can also combine the function with other para­met­ers. 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 re­quire­ment, use the COUNT() function.