PostgreSQL Aggregate Expressions

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.

Let’s use this table to explain different PostgreSQL aggregate functions.

Max

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

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

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

This query returns the total count of records in the sales table.

Avg

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 identical 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.

Breaking down the query:

SELECT Customer specifies the column name to retrieve from the table.

SUM(Quantity * cost) AS Total_Spent multiplies the Quantity and cost columns for each sale to calculate the total amount spent by the customer, and names the result column Total_Spent.

FROM sales specifies the table from which the query retrieves the data.

GROUP BY Customer clause groups the results by the Customer column.

This is the out put of the above query: