AVG
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.
💡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 |
+-----------+----------------------+