PostgreSQL Aggregation in Group By: SUM, AVG, MIN, MAX, and COUNT

Introduction

Learning how to use aggregation functions like SUM, AVG, MIN, MAX, and COUNT in PostgreSQL is crucial for efficiently summarizing and analyzing data. This tutorial demonstrates their power when used in combination with the GROUP BY clause.

Understanding Aggregation Functions

Aggregation functions compute a single result from a set of input values. Here’s a brief description of each:

• SUM: Calculates the total sum of a numeric column.
• AVG: Computes the average of a numeric column.
• MIN: Finds the smallest value in a column.
• MAX: Identifies the largest value in a column.
• COUNT: Counts the number of rows that match a specified condition.

Basic Aggregation Queries

Let’s start with some simple examples. We’ll use a fictional sales table named sales_data with columns date, region, and amount.

SELECT SUM(amount) AS total_sales
FROM sales_data;

This query calculates the total sales amount.

SELECT AVG(amount) AS average_sales
FROM sales_data;

Here, we obtain the average sales amount.

SELECT MIN(amount) AS minimum_sale,
       MAX(amount) AS maximum_sale
FROM sales_data;

This query fetches the smallest and largest sale amounts in one go.

SELECT COUNT(*) AS number_of_sales
FROM sales_data;

This determines the total number of sales transactions recorded.

Group By with Aggregation

The real power of these functions is revealed when we use them with the GROUP BY clause to summarize data across groups.

SELECT region,
       SUM(amount) AS total_sales
FROM sales_data
GROUP BY region;

This query gives us the total sales per region.

SELECT region,
       AVG(amount) AS average_sales
FROM sales_data
GROUP BY region;

Here, we find the average sales per region.

SELECT date_trunc('month', date) AS sale_month,
       SUM(amount) AS monthly_sales
FROM sales_data
GROUP BY sale_month;

The above query groups sales by month, rounding down dates to the first of the month, and then sums the amounts.

Advanced Group By Techniques

Beyond basic grouping, we can use advanced techniques to extract more insightful information.

SELECT region,
       COUNT(*),
       SUM(amount) AS total_sales,
       AVG(amount) AS average_sales,
       MIN(amount) AS minimum_sale,
       MAX(amount) AS maximum_sale
FROM sales_data
WHERE amount > 100
GROUP BY region
HAVING COUNT(*) > 5;

The combination of WHERE, GROUP BY, and HAVING clause filters results at different stages to only show regions with more than 5 sales greater than $100.

Joining Tables with Group By

When dealing with related tables, we can join them and perform groupings that cross boundaries of a single table.

SELECT s.region,
       p.category,
       SUM(s.amount) AS total_sales
FROM sales_data s
JOIN products p ON s.product_id = p.id
GROUP BY s.region, p.category;

This query sums sales by region and product category.

Window Functions vs. Aggregation

Another valuable tool is window functions, which provide more refined control than aggregation but cannot replace them for simple summarization tasks.

SELECT region,
       date,
       amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY date) AS running_total
FROM sales_data;

This example shows a running total of sales partitioned by region and ordered by date, without collapsing the rows into a single row per region.

Conclusion

Understanding and effectively using SUM, AVG, MIN, MAX, and COUNT functions with GROUP BY in PostgreSQL can significantly enhance data analysis efficiency. These powerful tools are the foundation of data manipulation and reporting.