Aggregate window functions allow users to efficiently generate output such as comparing specific rows output to average across all rows or calculating cumulative sum by applying the aggregate result to every row of the data. This often helps developers to make their models efficient. In this tutorial, we will learn about the multiple aggregate function with its practical usage in PostgreSQL.
Understanding Aggregate Function
Let’s assume a situation where you are the owner of the grocery store, and as you know being an owner you do have to manage a lot of responsibilities and the most complex one is managing the financial bills. But to get the return on investment you must need to calculate the expenditure ain’t you?
That’s where the PostgreSQL aggregate function comes into the picture, to ease all of your tasks! This function performs a calculation on a multiple set of values, and then it returns a single scaler value.
Now, let’s move to its syntax and know how it actually works.
Syntax
SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)Where AGGREGATE_FUNCTION replaces the specified aggregate function.
Types of PostgreSQL Aggregate Functions
Here we will learn about the types of aggregate functions:
- AVG()
- COUNT()
- MIN(), MAX()
- SUM()
Observe the below table named “Orders” to understand the aggregate functions better.
Also Read: An Easy Guide to SQL Ranking Window Functions
1. AVG() Function
Now, let’s back to our previous assumption, yes, the one where you were the owner of the grocery store. There must be a situation where you wanted to know what is actually your average income.
In the scenarios like this, you can use the AVG() aggregation function. This function is used to get the average value of the records which you have provided.
Syntax
SELECT AVG(column) AS average_result
FROM table
WHERE condition;Code language: SQL (Structured Query Language) (sql)Example
Let’s calculate how much on average you earn from the grocery store.
SELECT AVG(order_amount) AS average_order_amount
FROM orders;Code language: SQL (Structured Query Language) (sql)Output
As we can see the query returned the average order amount and well that’s not a bad earning.
2.COUNT() Function
Now that you know your average income, you must know how many orders you actually got so that you can work on it and increase your productivity.
COUNT() function can do your task effectively. It returns the count of the rows from the result set.
Syntax
SELECT COUNT(*) AS count_result
FROM table
WHERE condition;Code language: SQL (Structured Query Language) (sql)Example
Let’s know the count of the total order you got.
SELECT COUNT(*) AS total_orders
FROM orders;Code language: SQL (Structured Query Language) (sql)Output
As we can see the query returned the count of a total number of orders.
3. MIN() and MAX() Function
Suppose I am your competitor and my store is just beside yours if I have to compete with you I must know what is your minimum order amount and maximum order amount so that I can analyze it and put the most selling product at the front of the display.
To do so, I will use MIN(), and MAX() functions to ease my task. This function will return the minimum and maximum values from the specified arguments.
Syntax
SELECT MIN(column01) AS min_result
MAX(column02) AS max_result
FROM table
WHERE condition;Code language: SQL (Structured Query Language) (sql)Example
So, here is the query to calculate the minimum and maximum amount of your order.
SELECT MIN(order_amount) AS min_order_amount,
MAX(order_amount) AS max_order_amount
FROM orders;Code language: SQL (Structured Query Language) (sql)Output
As we can see, the query returned the minimum and maximum amount of the order.
4. SUM() Function
It is good practice to know how much exactly you have earned from your grocery store to calculate how much more you have to invest. This will save you from overspending.
To do so, you can use the SUM() function. This function will calculate the total sum of all values which you have specified.
Syntax
SELECT SUM(column) AS sum_result
FROM table
WHERE condition;Code language: SQL (Structured Query Language) (sql)Example
Let us know your total earnings with the help of the below query:
SELECT SUM(order_amount) AS total_sales_amount
FROM orders;Code language: SQL (Structured Query Language) (sql)Output
As you can see, the query returned the total amount of the orders placed.
Conclusion
In this tutorial, we have learned about the PostgreSQL Aggregate function with the help of practical usage. It is important for PostgreSQL developers to know about this topic so that they can implement the same on their tasks, which will save a lot of their time and result in highly efficient models during data manipulation. We hope you enjoyed it.