Postgres Avg Function in SQL

Introduction

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 AVG function 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”.

Prerequisites

• Beginner-level understanding of writing SQL for PostgreSQL (or MS SQL Server, Oracle, MySQL, etc.) using the free PG Admin tool or some other relational db admin tool, or by via code or script (compiled or not, object-oriented or not) languages like Python, PHP, Java, C#, ASP.Net, Javascript, VB.Net, Ruby, Node, etc.) that provides a database connection, as well as a method for sending it PL/SQL queries, to get data or make changes to your database.
• Understanding of the use of the most basic SQL (PL/SQL) statements, like SELECT, FROM, and GROUP BY.
• Knowledge of what a string is and what a function is.

What is the AVG() function?

The PostgreSQL AVG function returns the average of a set of numbers.

How do we use the function?

Syntax

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:

• (1) SELECT: We are telling Postgres’ SQL engine that we want an average of all values (because no WHERE statement) in the i_age field (column).
• (2) FROM: Specifying the languages table to get our data from.
• (3) 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.

Analysis of the SQL above:

• (1) SELECT: We are telling Postgres that we want an average of all values (respecting the HAVE statement below) in the i_grade field (column) and to name that result as i_grade_average.
• (2) FROM: Specifying the technologies table as the source of our data.
• (3) GROUP BY: Notifying PostgreSQL that we are grouping. The AVG function requires that we group of the data that Postgres is internally summing up and dividing by number of rows.
• (4) HAVING: This is where we ask PostgreSQL to only consider the rows that are a value of “Language” in the t_category_tech field.

Can you guess what the above query will return?

Notes:

• Look for our other articles that utilize the simple and useful AVG Postgres function.
• 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.

Conclusion

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.