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
, andGROUP 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
1 | 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.
id_technology | t_name | i_age |
---|---|---|
0 | Python | 8 |
1 | Java | 7 |
2 | C# | 9 |
3 | JavaScript | 8 |
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:
1 2 3 4 5 6 | SELECT AVG(i_age) FROM languages GROUP BY i_age; |
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 thei_age
field (column). - (2)
FROM
: Specifying thelanguages
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”:
id | t_name_user | t_name_tech | t_category_tech | i_grade |
---|---|---|---|---|
0 | Jimmy | Mongo | NoSQL | 2 |
1 | Frank | Mongo | NoSQL | 8 |
2 | Betty | MySQL | RDB | 4 |
3 | Gabe | MySQL | RDB | 6 |
4 | Tom | PostgreSQL | RDB | 8 |
5 | Phil | PostgreSQL | RDB | 10 |
6 | Hal | PostgreSQL | RDB | 9 |
7 | Wanda | Python | Language | 6 |
8 | Ken | Python | Language | 10 |
9 | Robert | PHP | Language | 4 |
10 | Paul | PHP | Language | 6 |
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.
1 2 3 4 5 6 7 8 9 10 | SELECT t_name_tech , AVG(i_grade) AS i_grade_average FROM technologies GROUP BY t_name_tech , i_GRADE; HAVING t_category_tech = 'Language' |
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 thei_grade
field (column) and to name that result asi_grade_average
. - (2)
FROM
: Specifying thetechnologies
table as the source of our data. - (3)
GROUP BY
: Notifying PostgreSQL that we are grouping. TheAVG
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 thet_category_tech
field.
Can you guess what the above query will return?
t_name_tech | t_category_tech | i_grade |
---|---|---|
Python | Language | 8 |
PHP | Language | 5 |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started