SQL SELECT AVG() Function

SQL AVG() function returns the average of values of a numeric column in a table.

AVG() Syntax

SELECT AVG(column_name)
  FROM table_name
WHERE condition;

SQL AVG() Example

Lets take an example to understand the working of AVG() function.

Table: Products

PRODUCT_ID    PRICE   QUANTITY
----------    -----   ---------
P101           100      10
P105           200      23
P106           200      55
P110           400      13
P123           100      90

The following SQL statement finds the average price of products.

SELECT AVG(PRICE)
FROM Products;

Result:

AVG(PRICE)
----------
200

SQL AVG() with where clause

We can find the average of only those rows which satisfy the given condition using where clause.

Table: Products

PRODUCT_ID    PRICE   QUANTITY
----------    -----   ---------
P101           100      10
P105           200      23
P106           200      55
P110           400      13
P123           100      90

The following SQL statement finds the average price of only those products where quantity is greater than 50.

SELECT AVG(PRICE)
FROM Products
WHERE QUANTITY > 50;

Result:

AVG(PRICE)
----------
150

Explanation: In the above example, only two rows satisfy the given condition (QUANTITY > 50), the product price on these rows are 200 and 100 respectively. Since the AVG() function is working on these two rows only, the average of 200 & 100 comes out as 150 in the output.

SQL AVG() function with GROUP BY clause

Similar to SUM() function, we can use the AVG() function with GROUP BY clause. Lets take an example.

Table: Customers

CUSTOMER_ID     ORDER_ID     AMOUNT   QUANTITY
-----------     --------     -------   --------
C101             1011         5000      10
C101             2011         2000      20
C134             1033         2000      20
C134             1045         1000      20
C199             1134         1000      50

The following SQL statement finds the average quantity of products ordered by each individual customers. In the following example, we are using SQL Alias to temporary rename the AVG(QUANTITY) column to “Avg Quantity” in the output.

SELECT CUSTOMER_ID, AVG(QUANTITY) AS "Avg Quantity"  
FROM Customers  
GROUP BY CUSTOMER_ID;

Result:

CUSTOMER_ID     Avg Quantity
-----------     -----------
C101             15
C134             20
C199             50