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
Leave a Reply