AVG

9min
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.
﻿
ℹ️ 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 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 expression
In 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 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 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 09 Oct 2023
Did this page help you?
FOR_MAX
COUNT
Docs powered by Archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: AVG() with a single expression
#Case 2: AVG() with a GROUP BY clause