Postgres Avg Function in SQL
In this article, we will show how to use the Postgres Avg function in SQL to find the average of a set of numbers, including:
- What? What does the
AVGfunction do when fed numbers?
- How? How do we utilize this function in our PostgreSQL SQL commands?
- Why? When would we use this function? We show a real world use case. Includes use of “GROUP BY”.
- Understanding of the use of the most basic SQL (PL/SQL) statements, like
- Knowledge of what a string is and what a function is.
What is the AVG() function?
AVG function returns the average of a set of numbers.
How do we use the function?
i_average = AVG(set_of_numbers);
Why and when would we use the AVG() function?
A simple example first
Let’s start with a simple example. Our initial dataset is a table called languages.
note: the ages above are not accurate. We chose these numbers to make it easy for you to average them in your head. Note, the “7” in our second row, averaged with the “9” in our third row are easy to average in your head to “8”.
Your mission, should you choose to accept it, and which we are going to do for you, is to find the average age of all four technology ages.
note for example below. Here is where we bring our knowledge of SQL GROUPing into the mix. You can think of it this way: If you want to average more than one number, you need to look at the group of numbers.
Let’s write the SQL code to use the
AVERAGE function to retrieve the average we want from that recordset:
How does the above work? We’ll go piece by piece:
SELECT: We are telling Postgres’ SQL engine that we want an average of all values (because no WHERE statement) in the
FROM: Specifying the
languagestable to get our data from.
GROUP BY: Telling PostgreSQL that we are grouping. The AVG() function requires grouping of the data.
The above query returns the integer, “8”.
Another example of how to use the “Average” function in SQL
The idea: We had employees grade the technologies used at our company and want to know the average grades. Here we will explore another relatively simple use of
AVG() while introducing the “HAVING” key word. Here’s our “raw” dataset with the table name of “technologies”:
High level planning: The following query will do a few things, including:
- (1) Only consider the rows in our data set where the _t_categorytech field (column) value is equal to “Language”. This is where the HAVING clause comes in handy. This yields four rows.
- (2) Return the value in the _t_nametech column (field) for those four rows because we want to know which technologies the grades go with.
- (3) Explicitly NOT caring WHO made the assessment, so no mention of the _t_nameuser field (column).
- (4) Get an average of the values of those 4 rows using the _igrade column (field).
- (5) Using GROUP BY to group the appropriate columns.
, AVG(i_grade) AS i_grade_average
t_category_tech = 'Language'
Analysis of the SQL above:
SELECT: We are telling Postgres that we want an average of all values (respecting the HAVE statement below) in the
i_gradefield (column) and to name that result as
FROM: Specifying the
technologiestable as the source of our data.
GROUP BY: Notifying PostgreSQL that we are grouping. The
AVGfunction requires that we group of the data that Postgres is internally summing up and dividing by number of rows.
HAVING: This is where we ask PostgreSQL to only consider the rows that are a value of “Language” in the
Can you guess what the above query will return?
- Look for our other articles that utilize the simple and useful
- Why did we name some of our variables and columns with a prefix of “i” or “t“? In this case, we use “i” to mean integer and “t” to mean text or string.
Here we learned how, why, and when to use the
AVG() function in PostgreSQL. We also used a real world example, so as to help you see the value and power of this mathematical function in your SQL.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started