Online College Courses for Credit

+
AVG to Average Values

AVG to Average Values

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that shows the average of a given column within a table.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the AVG function to average values of a given column within a table in two parts:

  1. Getting Started
  2. Dealing with 0s and NULL

1. Getting Started

The AVG function is used to calculate the average value of a set of values. This is one of the most commonly used aggregate functions. If we wanted to find the average total from the invoice table, we could run something like:

SELECT AVG(total)
FROM invoice;

This would return a value like:

File:11303-1350-1.png

This may not be entirely readable and being that the total is a price, it may make more sense to limit the result to 2 decimal places. Unique to PostgreSQL, we can cast the result as follows:

SELECT AVG(total)::numeric(10,2)
FROM invoice;

Above, we will be casting it to 10 digits with 2 digits after the decimal:

File:11304-1350-2.png

In other databases, you may see the use of functions like ROUND to round the value or TRUNC to truncate the value to a certain position without rounding.

There are times that we may want to find averages of values with criteria being applied. For example, if we looked at all of the orders that were set by customer_id equal to 2:

SELECT *
FROM invoice
WHERE customer_id = 2;

We should find 7 rows with a total value in each:

File:11305-1350-3.png

If we calculated the average to 2 decimal places, it would look like this:

SELECT AVG(total)::numeric(10,2)
FROM invoice
WHERE customer_id = 2;

File:11306-1350-4.png


2. Dealing with 0s and NULL

What is important to note is that the average only consists of values that are not NULL. For example, if we set one of the 7 invoices to have the total as NULL:

UPDATE invoice
SET total = NULL
WHERE invoice_id = 1;

If we queried for the customer_id = 2, we would see that the total has no value which is different than a 0:

File:11307-1350-5.png

Calculating the average would result in the following:

SELECT AVG(total)::numeric(10,2)
FROM invoice
WHERE customer_id = 2;

File:11308-1350-6.png

However, if we updated that same invoice to set the total to 0 like:

UPDATE invoice
SET total = 0
WHERE invoice_id = 1;

Running the same calculation:

SELECT AVG(total)::numeric(10,2)
FROM invoice
WHERE customer_id = 2;

File:11309-1350-7.png

In the first case, even though there are 7 rows, the null value does not count so it does the following calculation (14 + 9 + 2 + 4 + 6 + 1)/6 = 6.00. In the second case, since the 0 is added instead of the null value, it counts it as part of the calculation as: (14 + 9 + 2 + 4 + 6 + 1 + 0)/7 = 5.14

Similar to the SUM function, if we only have NULL values, using the AVG would return NULL.

SELECT AVG(total)
FROM invoice
WHERE invoice_id < 1;

File:11310-1350-8.png

For us to have a 0 returned, we would need to use the COALESCE function that would return the second argument if the first argument was NULL.

SELECT COALESCE(AVG(total),0)
FROM invoice
WHERE invoice_id < 1;

File:11311-1350-9.png


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The AVG function allows us to calculate the average of a set of values.