AVG

MySQL

AVG(column_name)

• column_name: This parameter specifies the column of which the average will be calculated. In the AVG() function, this column should contain numeric type data because an average cannot be calculated on non-numeric types.

Example

SELECT AVG(price)
FROM products;

Output

| AVG(price) |
|------------|
|    45.00   |

Explanation

The above SQL query calculates the average price of all products in the ‘product’ table. The AVG function adds up all prices and divides it by the number of prices to return the average.

PostgreSQL

AVG(expression) OVER ([PARTITION BY partition_expression] [ORDER BY sort_expression [ASC | DESC]] [ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | UNBOUNDED FOLLOWING])

• expression: The specific column or set of columns for which the average is to be calculated. If multiple columns are specified, they must be enclosed within parentheses.
• partition by partition_expression: This divides the result set into partitions (or groups) based on one or more specified columns. Each partition is processed individually for the calculation of the average.
• order by sort_expression [asc | desc]: This clause sorts the rows in each partition based on the specified column(s). The order can be ascending (ASC) or descending (DESC).
• rows between unbounded preceding and current row | unbounded following: This clause defines a sliding window within each partition to perform the calculation. ‘UNBOUNDED PRECEDING’ includes all previous rows, ‘CURRENT ROW’ includes only the current row, and ‘UNBOUNDED FOLLOWING’ includes all subsequent rows.

Example

SELECT AVG(salary) AS Average_Salary
FROM employees;

Output

  Average_Salary
------------------
     55000

Explanation

The AVG function in PostgreSQL calculates the average of a set of values. In this example, it is used to calculate the average salary from all salaries in the employees table. The result, 55000, is the average salary of all employees.

SQL Server

AVG( [ ALL | DISTINCT ] expression ) OVER ( [ partition_by_clause ] order_by_clause )

• all | distinct: This parameter determines whether or not duplicates should be included in the AVG calculation. Using ALL will include duplicates, while DISTINCT will exclude any duplicate values.
• expression: This parameter refers to the specific column or set of columns that you want to calculate the average for.
• over: This keyword initiates the analytical function, allowing for more advanced SQL queries than the traditional aggregate function. It defines a window or set of rows where the function will be applied.
• partition_by_clause: This optional clause divides the result set into partitions (or smaller groups of rows). The AVG function is then applied to each partition separately and independently.
• order_by_clause: This optional clause determines the order in which the rows within each partition should be ordered when the AVG function is applied. Depending on the function, this may or may not affect the result. For the AVG function specifically, the order_by_clause does not affect the result unless specified with other keywords such as ROWS or RANGE.

Example

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

Output

AverageSalary
-------------
57500

Explanation

The AVG() function is used to compute the average value of the Salary column of the Employees table.

Oracle

AVG( [ DISTINCT | ALL ] expression ) OVER ( [ partition_by_clause ] order_by_clause )

• distinct: The DISTINCT keyword ensures that only unique values in the expression parameters are considered when calculating the average.
• all: The ALL keyword means that all values, including duplicates, in the expression parameters are considered when calculating the average. This is the default behavior if neither DISTINCT nor ALL are specified.
• expression: This refers to the column or set of columns that are to be averaged. It must come within the AVG function and could be numerical columns or expressions that result in a numerical value.
• over: The OVER keyword is used to calculate the average on a particular “window” of rows related to the current row. It’s used for creating more complex calculations on the query result set.
• partition_by_clause: This is an optional clause that divides the result set produced by the FROM clause into partitions to which the AVG function is applied. In other words, the AVG function is computed separately for each partition.
• order_by_clause: This is also an optional clause and it specifies the order in which rows are to be ordered in each partition when the function is calculated. The order_by_clause does not guarantee the order of the result set. Instead, it guarantees the order of the rows in each partition.

Example

SELECT AVG(salary)
FROM employees;

Output

AVG(SALARY)
-----------

35000

Explanation

In the above example, the SQL AVG() function is used to calculate the average value from the “salary” column in the “employees” table. The result is 35000, indicating the average salary of all employees in the table.

SQLite

AVG(expression)

• expression: It is the specific column or set of values on which the AVG function is to be determined. The values in this column must be of a numeric data type for the averaging operation to be performed.

Example

SELECT AVG(price)
FROM products;

Output

85.25

Explanation

The SELECT AVG(price) statement calculates the average price of all products in the ‘products’ table.