## DEV Community

Johnson Ikechukwu

Posted on

# Maximize Data Efficiency: PostgreSQL's Aggregate Expressions at Your Fingertips!

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 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:

Customer Total_Spent
Varrick 176000
Tolf 150
Kuvira 8000
Kalu 88000
Isujah 130000
Zion 750000