SQL AVG() Function
The AVG() function returns the average value of a numeric column.
The SQL AVG() function calculates the average value of a set of values within a specified column of a table. It’s frequently utilized in SQL queries to retrieve statistical information or aggregate data.
The syntax for the AVG() function is:
|
1 |
AVG(column_name) |
Here, column_name represents the name of the column for which you wish to compute the average.
For instance, consider a table named “sales” with a column called “amount_sold” containing sales amounts. You can employ the AVG() function to determine the average sales amount:
|
1 2 |
SELECT AVG(amount_sold) AS average_sales FROM sales; |
This query will furnish the average sales amount from the “amount_sold” column of the “sales” table.
It’s crucial to note that the AVG() function operates solely on numeric data types. Attempting to apply it to non-numeric data types, such as strings, will result in an error.
Suppose we have a table named “transactions” that records the purchase amounts for various products. Here’s what the table might look like:
| transaction_id | product_id | amount |
|---|---|---|
| 1 | 101 | 50.00 |
| 2 | 102 | 75.00 |
| 3 | 103 | 100.00 |
| 4 | 101 | 60.00 |
| 5 | 104 | 45.00 |
We want to find the average purchase amount across all transactions. We can use the AVG() function for this:
|
1 2 |
SELECT AVG(amount) AS average_purchase_amount FROM transactions; |
This query will compute the average of all the values in the “amount” column and return the result with the alias “average_purchase_amount”.
After executing the query, if the average purchase amount is $66.00, for instance, the result set would look like this:
| average_purchase_amount |
|---|
| 66.00 |
This means that on average, customers spend $66.00 per transaction.
You can also use the AVG() function with a GROUP BY clause to calculate averages within specific groups. For example, if you want to find the average purchase amount for each product:
|
1 2 3 |
SELECT product_id, AVG(amount) AS average_purchase_amount FROM transactions GROUP BY product_id; |
This query will calculate the average purchase amount for each distinct product, grouping the results accordingly.
So, the AVG() function provides a straightforward way to calculate the average value of a set of numeric data in SQL queries.
