SQL AVG Function

The SQL AVG is an aggregate function used to calculate the average value of a numeric column. It returns the average of all non-NULL values.

Syntax

The basic syntax for the AVG function is as follows:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Or the syntax for the AVG function when used with the GROUP BY clause:

SELECT colunm1_name, column2_name, ... columnN_name, AVG(column_name)
FROM table_name
WHERE condition
GROUP BY column1_name, column2_name, ... columnN_name

Parameters:

• colunm1_name, column2_name, ... columnN_name: Columns that are not encapsulated within the AVG function and must be included in the GROUP BY clause.

• column_name: This is the column that will be averaged.

Demo Table

To understand the AVG function, let us consider that we have a table called "Employee_dwh" with the following data:

AVG Function With Single Expression - Example

The following SQL statement will calculate the average of all daily working hours.

SELECT AVG(daily_working_hours) AS 'Average Daily Working Hours'
FROM employee_dwh;

After the execution, the output will look as follows:

+-----------------------------+
| Average Daily Working Hours |
+-----------------------------+
| 6,5                         |
+-----------------------------+

AVG With DISTINCT Clause - Example

You can also use the DISTINCT clause with the AVG function.

The following SQL statement will return the average daily working hours for unique values. For our example, it will be the average of "6, 4, 8, 10".

SELECT AVG(DISTINCT daily_working_hours) AS 'Average Unique Hours'
FROM employee_dwh;

The output will be as follows:

+---------------------+
| Average Unique Hour |
+---------------------+
| 7                   |
+---------------------+

AVG With a Formula - Example

The expression encapsulated within the AVG function can be a formula.

The following SQL statement will calculate the average daily working hours by minutes.

SELECT AVG(daily_working_hours * 60) AS 'Average Daily Working Minutes'
FROM employee_dwh;

After the execution, the output will look as follows:

+-------------------------------+
| Average Daily Working Minutes |
+-------------------------------+
| 390                           |
+-------------------------------+

AVG With GROUP BY Clause - Example

In real-life situations you will need to use the AVG function with the GROUP BY clause. This happens when you have columns listed in the SELECT statement that are not encapsulated in the AVG function.

The following SQL statement will return the average daily working hours for each employee.

SELECT name, AVG(daily_working_hours) AS 'Average Daily Working Hours'
FROM employee
GROUP BY  name;

After the execution, the output will look as follows:

+------------------+-----------------------------+
| name             | Average Daily Working Hours |
+------------------+-----------------------------+
| James Smith      | 5                           |
| Maria Martinez   | 5                           |
| Donna Williams   | 8                           |
| Robert Hernandez | 10                          |
| James Johnson    | 6                           |
+------------------+-----------------------------+

Note: Every column listed in the SELECT statement and not encapsulated in the AVG function must be listed in the GROUP BY clause.