Understanding SQL Aggregation: Selecting Maximum Values and Handling Non-Aggregated Columns

Introduction

In relational databases, aggregating data is a common task used to summarize information. One of the fundamental aggregation functions is MAX(), which retrieves the maximum value from a set of values in a column. However, using MAX() can present challenges when combined with other columns that aren’t part of an aggregate function or included in the GROUP BY clause. This tutorial explores how to correctly use SQL aggregation to find maximum values while dealing with non-aggregated columns, and provides multiple techniques to address this common issue.

Aggregating Data

When using aggregate functions such as SUM(), AVG(), MIN(), or MAX(), SQL requires that any column in the select list that isn’t aggregated must be included in a GROUP BY clause. This requirement ensures that each group can unambiguously determine which non-aggregated values to include.

Consider you have a table named makerar with columns cname, wmname, and avg. Your goal is to find the maximum value of avg for each unique cname.

Incorrect Approach

Attempting a direct aggregation like this will result in an error:

SELECT cname, wmname, MAX(avg)
FROM makerar
GROUP BY cname;

This query fails because wmname isn’t part of any aggregate function nor included in the GROUP BY clause. The SQL engine cannot determine which value to return for wmname.

Correcting the Query

To properly address this issue, you must either:

1. Aggregate Non-Aggregated Columns: Include all non-aggregated columns in the GROUP BY clause.

2. Subquery and Join Approach: Calculate aggregates in a subquery and then join it back to retrieve additional information.

3. Use Window Functions: Utilize window functions for more complex data retrieval without losing detail.

1. Subquery and Join

First, compute the maximum value of avg per cname using a subquery:

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
) t 
JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg;

This approach joins the original table with a subquery result to fetch corresponding wmname values for each maximum avg.

2. Window Functions

Window functions provide an alternative solution by allowing calculations across sets of rows related to the current row.

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar;

While this method includes all records with a partitioned maximum value, it simplifies fetching data without grouping. However, if you need only one record per group:

SELECT DISTINCT m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY cname ORDER BY avg DESC) AS rn 
    FROM makerar
) t 
JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1;

Here, ROW_NUMBER() helps select a single record per group by ordering and ranking.

3. PostgreSQL Specific: DISTINCT ON

PostgreSQL offers the DISTINCT ON clause to directly obtain unique records based on specified columns:

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM makerar 
ORDER BY cname, avg DESC;

This approach efficiently filters distinct rows based on ordered criteria.

Conclusion

Aggregating data while preserving additional column information can be challenging in SQL. Understanding and applying techniques like subqueries, joins, window functions, and PostgreSQL-specific features such as DISTINCT ON enables effective data manipulation and retrieval. Selecting the right method depends on your specific requirements and database system capabilities.