​
Overview

The AVG() function lets you calculate the average value of records.

The input and return types we support can be seen in the table below.

avg types

If the input type is 32-bit, then the result will be 64-bit.

💡Special cases: Returns NaN if the input contains a NaN.

​
Examples

In this example, we will use an orders table that stores details of the purchase transactions:

CREATE TABLE orders (
    orderid int,
    custname text,
    orderproduct text,
    ordertotal real
);
INSERT INTO orders (orderid, custname, orderproduct, ordertotal)
VALUES
(9557411, 'Maya', 'Jeans', 10.5),
(9557421, 'Aaron', 'T-Shirt', 9.2),
(9557451, 'Alex', 'Hat', 10.8),
(9557311, 'Will', 'Hat', 8.5),
(9557321, 'Will', 'T-Shirt', 12.15),
(9557351, 'Maya', 'T-Shirt', 9.5),
(9557221, 'Maya', 'Jeans', 11.02),
(9557251, 'Alex', 'Jeans', 11.09),
(9557231, 'Aaron', 'Hat', 14.56),
(9557281, 'Aaron', 'Hat', 12.15),
(9557291, 'Will', 'T-Shirt', 13.1);

SELECT * FROM orders;

The above query will show the following table:

+----------+-----------+---------------+-------------+
| orderid  | custname  | orderproduct  | ordertotal  |
+----------+-----------+---------------+-------------+
| 9557411  | Maya      | Jeans         | 10.5        |
| 9557421  | Aaron     | T-Shirt       | 9.2         |
| 9557451  | Alex      | Hat           | 10.8        |
| 9557311  | Will      | Hat           | 8.5         |
| 9557321  | Will      | T-Shirt       | 12.15       |
| 9557351  | Maya      | T-Shirt       | 9.5         |
| 9557221  | Maya      | Jeans         | 11.02       |
| 9557251  | Alex      | Jeans         | 11.09       |
| 9557231  | Aaron     | Hat           | 14.56       |
| 9557281  | Aaron     | Hat           | 12.15       |
| 9557291  | Will      | T-Shirt       | 13.1        |
+----------+-----------+---------------+-------------+

​
#Case 1: AVG() with a single expression

In the first example, we want to calculate the average amount of all orders that customers have paid:

SELECT AVG(ordertotal) AS "Order Total Average"
FROM orders;

It will return the following output:

+---------------------+
| Order Total Average |
+---------------------+
| 11.142727331681685  |
+---------------------+

​
#Case 2: AVG() with a GROUP BY clause

The following example uses the AVG() function and GROUP BY clause to calculate the average amount paid by each customer:

  • First, the GROUP BY clause divides orders into groups based on customers

  • Then, the AVG function is applied to each group.

SELECT custname AS "Customer", AVG (ordertotal) AS "Total Price Average"
FROM orders
GROUP BY custname;

It will display the output as shown below:

+-----------+----------------------+
| Customer  | Total Price Average  |
+-----------+----------------------+
| Aaron     | 11.96999994913737    |
| Alex      | 10.945000171661377   |
| Will      | 11.25                |
| Maya      | 10.34000015258789    |
+-----------+----------------------+

💡 You can use the cast operator like::NUMERIC(10,2) to add two decimal numbers after the comma:

SELECT custname AS "Customer", AVG (ordertotal)::NUMERIC(10,2) AS "Total Price Average"
FROM orders
GROUP BY custname;

The result will trim and round two numbers after the comma:

+-----------+----------------------+
| Customer  | Total Price Average  |
+-----------+----------------------+
| Aaron     | 11.97                |
| Alex      | 10.95                |
| Will      | 11.25                |
| Maya      | 10.34                |
+-----------+----------------------+
OverviewBOOL_AND