Filtering Rows Using Aggregate Functions in PostgreSQL

PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. Filtering rows based on conditions is a regular operation in database administration.

Although filtering rows by each column value is easily done, more advanced filtering needs which may require aggregate functions usage occur more often. In this article, we will go into detail about filtering rows using aggregate functions in PostgreSQL, and see some example queries of filtering rows using aggregate functions.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. These functions are particularly useful when dealing with large datasets or when summarizing data across multiple rows. Common aggregate functions include:

• SUM
• COUNT
• AVG
• MIN
• MAX

Filtering Rows Using Aggregate Functions

The ability of the aggregate functions to work can be seen better when they are applied together with the HAVING clause to filter rows based on aggregated values. The HAVING condition works in the same way as the WHERE condition but is designed specifically to filter collections of rows returned by aggregate queries.

Now we do some practical examples to display the process of adding aggregate functions and filtering rows.

Let's start by creating a simple table, putting some sample data in, and then showing a few filters of rows using aggregate functions in PostgreSQL.

Create Table

CREATE TABLE sales (
    salesperson VARCHAR(50),
    sales_amount NUMERIC
);

Insert Data

INSERT INTO sales (salesperson, sales_amount) VALUES
('Minal', 5000),
('Priyanshi', 8000),
('Mridul', 7000),
('Asad', 12000),
('Maram', 6000),
('Mahi', 10000);

Output:

Now, we have a table named "sales" with some sample data.

Examples of Filtering Rows Using Aggregate Functions in PostgreSQL

Example 1: Filtering Rows Based on Total Sales Greater Than 10,000.

Query:

SELECT salesperson, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson
HAVING SUM(sales_amount) > 10000;

Explanation:

• SELECT salesperson, SUM(sales_amount) AS total_sales: It selects the salesperson column and calculates the total sales amount for each salesperson using the SUM aggregate function. The alias "total_sales" is assigned to the calculated sum.
  
• FROM sales: Specifies the table from which the data will be retrieved.
  
• GROUP BY salesperson: Groups the results by the salesperson column, so that the aggregate function operates on each distinct salesperson.
  
• HAVING SUM(sales_amount) > 10000: This clause filters the grouped rows based on the aggregated total sales amount. It ensures that only groups with a total sales amount greater than $10,000 are included in the final result set.

Output:

This query will return salespersons who have achieved a total sales amount greater than 10,000.

Example 2: Finding the Average Sales Amount.

Query:

SELECT AVG(sales_amount) AS average_sales
FROM sales;

Explanation:

• SELECT AVG(sales_amount) AS average_sales: It calculates the average sales amount for each salesperson using the AVG aggregate function. The alias "average_sales" is assigned to the calculated average.
  
• FROM sales: This specifies the table which is sales here.

Output:

This query calculates the average sales amount across all transactions.

Example 3: Identifying the Maximum Sales Amount by Salesperson.

Query:

SELECT salesperson, MAX(sales_amount) AS max_sales
FROM sales
GROUP BY salesperson;

Explanation:

• SELECT salesperson, MAX(sales_amount) AS max_sales: It selects the salesperson column and calculates the maximum sales amount for each salesperson using the MAX aggregate function. The alias "max_sales" is assigned to the calculated maximum sales.
  
• FROM sales: Specifies the table from which the data will be retrieved.
  
• GROUP BY salesperson: Group the results by the salesperson column, thereby performing aggregate operation on each distinct salesperson.

Output:

Here, we find the maximum sales amount for each salesperson.

Example 4: Counting the Number of Transactions for Each Salesperson.

Query:

SELECT salesperson, COUNT(*) AS transaction_count
FROM sales
GROUP BY salesperson;

Explanation:

• SELECT salesperson, COUNT(*) AS transaction_count: It selects the salesperson column calculates the count of transactions for each salesperson using the COUNT(*) aggregate function. The alias "transaction_count" is assigned to the count.
  
• FROM sales: Specifies the table from which the data will be retrieved.
  
• GROUP BY salesperson: Group the results by the salesperson column, thereby performing aggregate operation on each distinct salesperson.

Output:

This query counts the number of transactions made by each salesperson.

Example 5: Find the minimum sales amount for each salesperson.

Query:

SELECT salesperson, MIN(sales_amount) AS min_sales
FROM sales
GROUP BY salesperson;

Explanation:

• SELECT salesperson, MIN(sales_amount) AS min_sales: It selects the salesperson column and calculates the minimum sales amount for each salesperson using the MIN aggregate function. The alias "min_sales" is assigned to the calculated minimum sales.
  
• FROM sales: Specifies the table from which the data will be retrieved.
  
• GROUP BY salesperson: Group the results by the salesperson column, thereby performing aggregate operation on each distinct salesperson.

Output:

This query retrieves the minimum sales amount recorded for each salesperson in the "sales" table.

Conclusion

The capability of PostgreSQL to go filtering rows with aggregate functions offers a wide range of data analysis and manipulation possibilities. Regardless of the use of aggregate functions for data across rows or for detecting patterns within your dataset, you can extract information meaningfully in a matter of seconds. Through the mechanisms detailed in the present article you'll be conversant with complex data filtering techniques and this way, you won't hesitate to face difficult tasks.