Skip to main content
Log inGet a demo
Back to SQL Dictionary
Aggregate functions

SQL AVG

What is SQL AVG?

The SQL AVG aggregate function is used to calculate the average (mean) value of a specific numeric column in a table. It provides insight into the central tendency of numerical data, making it useful for statistical analysis.

When you would use it

You would use the AVG function when you want to find the average value of a numeric column. This function is particularly useful when you need to determine the average value of data, such as calculating the average salary, temperature, or grades.

Syntax

The basic syntax for using the AVG aggregate function in an SQL query is as follows:

SELECT AVG(column_name)
FROM table_name
WHERE condition;
  • SELECT: Specifies the result set.
  • AVG(column_name): Calculates the average value of the specified column.
  • FROM: Specifies the table from which you want to retrieve data.
  • WHERE: Specifies the condition for filtering rows (optional).

Parameter values

  • column_name: The name of the numeric column for which you want to calculate the average.
  • table_name: The name of the table containing the column.
  • condition: (Optional) A condition to filter rows before calculating the average. If omitted, the average is computed over all rows in the table.

Example query

Let's assume we have a table named "product_sales" with columns: "sale_id," "product_id," and "sale_amount." Here's an example query using the AVG function:

SELECT AVG(sale_amount)
FROM product_sales
WHERE product_id = 101;

In this query, we're calculating the average sale amount for products with product_id equal to 101.

Example table response

Suppose the "product_sales" table contains the following data:

sale_idproduct_idsale_amount
110150.00
210275.00
310160.00
410145.00

The query would return:

AVG(sale_amount)
51.6667

The average sale amount for products with product_id 101 is approximately $51.67.

Use cases

The AVG aggregate function is valuable in various scenarios, including:

  1. Calculating the average of numerical data, such as average test scores for students or average revenue per product.
  2. Analyzing central tendencies in data to make informed decisions, such as assessing the average temperature over a month.
  3. Creating summary reports and generating statistics for data analysis.

SQL languages this is available for

The AVG aggregate function is a standard feature supported by all major SQL database management systems (DBMS) and SQL-based languages, including but not limited to:

  • MySQL
  • PostgreSQL
  • Oracle Database
  • Microsoft SQL Server
  • SQLite
  • IBM Db2

In summary, the AVG aggregate function is a fundamental tool for calculating the average value of numeric data in SQL databases. It is available in all major SQL database systems and provides valuable insights into the central tendency of numerical columns.

Related

SQL COUNT

SQL SUM

SQL DISTINCT

Ready to put your SQL knowledge to work?

Practice writing SQL to call data from the warehouse and sync it into Google Sheets in this 5 minute interactive demo.

Hightouch Audiences user interface.