Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL AVG

The `AVG` function in PostgreSQL is a mathematical function used to calculate the average value of a set of numbers. It is commonly utilized in SQL queries to derive the mean value from a column of numeric data.

Usage

The `AVG` function is used when you need to find the average of a particular column in a database table. It is often applied in aggregate queries for data analysis.


SELECT AVG(column_name)
FROM table_name
[WHERE condition];

In this syntax, `AVG(column_name)` computes the average of the specified column, optionally filtered by a `WHERE` condition. The `HAVING` clause can also be used after `GROUP BY` to filter grouped results.

Examples

1. Basic Average Calculation


SELECT AVG(salary)
FROM employees;

This example calculates the average salary from the `employees` table.

2. Average with a Condition


SELECT AVG(salary)
FROM employees
WHERE department = 'Sales';

Here, the average salary is calculated for employees in the 'Sales' department only.

3. Average with Grouping


SELECT department, AVG(salary)
FROM employees
GROUP BY department;

This example calculates the average salary for each department, grouping the results by the department column.

4. Precision Handling with Casting


SELECT AVG(salary::numeric)
FROM employees;

In this example, the `salary` column is cast to a `numeric` type to improve precision in the average calculation.

Tips and Best Practices

  • Handle NULL values. The `AVG` function automatically ignores `NULL` values, ensuring they do not affect the calculation.
  • Use with GROUP BY. Combine `AVG` with `GROUP BY` to get averages for categories or groups, such as departments or regions.
  • Casting for precision. Consider casting your column to a `numeric` type if you need more precision in your average calculations, as shown in the example above.
  • Combine with other functions. Use `AVG` alongside other aggregate functions like `SUM` or `COUNT` for comprehensive data analysis.
  • Performance Considerations. When using the `AVG` function on large datasets, consider indexing strategies to enhance query performance.
  • Data Type Considerations. The `AVG` function returns a `double precision` type, which should be considered when handling the result's data type.