AVG

12min
In this article, you will learn how to use the AVG() function to calculate the average of a set of values.
OverviewThe AVG() function lets you calculate the average value of records.
The input and return types we support can be seen in the table below.
﻿
ℹ️ If the input type is 32-bit, then the result will be 64-bit.
💡Special cases: Returns NaN if the input contains a NaN.
ExamplesIn this example, we will use an orders table that stores details of the purchase transactions:
Create a Table
|
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);
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);
﻿
Display the Table
|
SELECT * FROM orders;
SELECT * FROM orders;
﻿
The above query will show the following table:
The orders 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        |
+----------+-----------+---------------+-------------+
+----------+-----------+---------------+-------------+
| 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 expressionIn the first example, we want to calculate the average amount of all orders that customers have paid:
Select avg
|
SELECT AVG(ordertotal) AS "Order Total Average"
FROM orders;
SELECT AVG(ordertotal) AS "Order Total Average"
FROM orders;
﻿
It will return the following output:
Output
|
+---------------------+
| Order Total Average |
+---------------------+
| 11.142727331681685  |
+---------------------+
+---------------------+
| Order Total Average |
+---------------------+
| 11.142727331681685  |
+---------------------+
﻿
#Case 2: AVG() with a GROUP BY clauseThe 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 avg
|
SELECT custname AS "Customer", AVG (ordertotal) AS "Total Price Average"
FROM orders
GROUP BY custname;
SELECT custname AS "Customer", AVG (ordertotal) AS "Total Price Average"
FROM orders
GROUP BY custname;
﻿
It will display the output as shown below:
Output
|
+-----------+----------------------+
| Customer  | Total Price Average  |
+-----------+----------------------+
| Aaron     | 11.96999994913737    |
| Alex      | 10.945000171661377   |
| Will      | 11.25                |
| Maya      | 10.34000015258789    |
+-----------+----------------------+
+-----------+----------------------+
| 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:
Pgsql
|
SELECT custname AS "Customer", AVG (ordertotal)::NUMERIC(10,2) AS "Total Price Average"
FROM orders
GROUP BY custname;
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:
Pgsql
|
+-----------+----------------------+
| Customer  | Total Price Average  |
+-----------+----------------------+
| Aaron     | 11.97                |
| Alex      | 10.95                |
| Will      | 11.25                |
| Maya      | 10.34                |
+-----------+----------------------+
+-----------+----------------------+
| Customer  | Total Price Average  |
+-----------+----------------------+
| Aaron     | 11.97                |
| Alex      | 10.95                |
| Will      | 11.25                |
| Maya      | 10.34                |
+-----------+----------------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
FOR_MAX
ABS
Docs powered by archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: AVG() with a single expression
#Case 2: AVG() with a GROUP BY clause