In PostgreSQL, aggregate functions calculate values across sets of data and return a single result. These functions allow you to perform tasks like counting, summing, averaging, and finding maximum or minimum values within a set of rows when used with the SELECT
statement and GROUP BY
clause. These functions produce a single result for an entire group of tables and create a summarized set of results based on a group of rows.
PostgreSQL offers the following aggregate functions:

MAX()
 Computes the maximum of input values. 
MIN()
 Computes the minimum of input values. 
SUM()
 Computes the sum of input values. 
COUNT()
 Computes the number of input rows. 
AVG()
 Computes the average (arithmetic mean) of all the input values.
Example
Consider the following sales
table as an example to demonstrate how these aggregate functions work.
Transaction_ID 
Customer 
Product 
Quantity 
Cost 

041  Varrick  Iphone Xr  2  88000 
021  Tolf  Samsung S8  2  75000 
033  Kuvira  Airpods  3  4000 
001  Kalu  Iphone X  1  44000 
456  Isujah  HP Laptop  1  65000 
026  Zion  MacBook Air  3  250000 
Let's use this table to explain different PostgreSQL aggregate functions.
Max
SELECT MAX(Quantity * Cost) AS Max_Spent FROM sales;
This query calculates the maximum amount spent on a single comodity in the sales
table by multiplying the Quantity
column with the cost
column and then determines the maximum value.
Min
SELECT MIN(Quantity * Cost) AS Min_Spent FROM sales;
The minimum amount spent on a single product in the sales
table is calculated by this query, which multiplies the Quantity
column with the Cost
column and then finds the minimum value.
Sum
SELECT SUM(Quantity * cost) AS Total_Spent FROM sales;
Calculating the total amount spent on all sales in the sales table involves multiplying the Quantity
column with the cost
column for each sale and then summing these values using this query, which labels the result as Total_Spent
.
Count
SELECT COUNT(*) FROM sales;
This query returns the total count of records in the sales
table.
Avg
SELECT AVG(Quantity * Cost) AS Average_Spent FROM sales;
This query finds the average amount spent per sale in the sales table by calculating the product of the Quantity
and cost
columns for each sale and then averaging these values.
GROUP BY
The GROUP BY
clause in Postgresql is used to arrange data into groups. This clause is often combined with aggregate functions to perform operations on each group of data. Essentially, GROUP BY
allows you to summarize data by grouping rows that have the same values in specified columns.
Let's use the sales
table to find out how much each customer spent in total.
SELECT Customer, SUM(Quantity * cost) AS Total_Spent
FROM sales
GROUP BY Customer;
Breaking down the query:

SELECT Customer
specifies the column name to retrieve from the table. 
SUM(Quantity * cost) AS Total_Spent
multiplies theQuantity
andcost
columns for each sale to calculate the total amount spent by the customer, and names the result columnTotal_Spent
. 
FROM sales
specifies the table from which the query retrieves the data. 
GROUP BY Customer
clause groups the results by theCustomer
column.
This is the out put of the above query:
Customer  Total_Spent 

Varrick  176000 
Tolf  150 
Kuvira  8000 
Kalu  88000 
Isujah  130000 
Zion  750000 
Top comments (0)