Exploring Aggregate Functions, GROUP BY, and the Power of HAVING in SQL

Summarizing and Analyzing Large Data Volumes

In relational systems, the ability to extract raw data is only the first step. The true strategic value of a database emerges when we can transform millions of transactional records into summarized information, consolidated metrics, and management indicators for decision‑making.

In this article, we will detail how aggregate functions work, the importance of grouping via GROUP BY, and the correct application of the HAVING clause for group filtering.

The Role of Aggregate Functions

Aggregate functions operate over a set of rows returned by a query and return a single consolidated summarized value. The main functions supported by virtually all relational DBMS on the market are:

• COUNT(): Returns the number of records or rows that meet a given criteria.
• SUM(): Sums the numeric values of a specific column.
• AVG(): Calculates the arithmetic mean of the values in a numeric set.
• MAX() and MIN(): Identify, respectively, the largest and smallest value present in a column.

Practical example of extracting general metrics:

SELECT COUNT(order_id) AS total_pedidos, SUM(total_amount) AS faturamento_bruto, AVG(total_amount) AS ticket_medio, MAX(total_amount) AS maior_pedido FROM orders WHERE order_date >= ADD_MONTHS(SYSDATE, -1);

Grouping Data with the GROUP BY Clause

When we need to calculate metrics segmented by specific categories (such as revenue per month, number of customers per city, or average salary per department), we use the GROUP BY clause.

A fundamental rule of standard SQL is that any column selected in the query that is not involved in an aggregate function must appear in the GROUP BY clause.

Practical grouping example by category:

SELECT c.category_name, COUNT(p.product_id) AS qtd_produtos, AVG(p.unit_price) AS preco_medio FROM products p JOIN categories c ON p.category_id = c.id GROUP BY c.category_name ORDER BY qtd_produtos DESC;

The Critical Difference Between WHERE and HAVING

One of the most common mistakes made by beginner and intermediate SQL programmers is confusion about when to use the WHERE filter and when to use the HAVING filter.

• WHERE: Filters rows from the original table before grouping and aggregation are performed. Does not accept aggregate functions.
• HAVING: Filters resulting groups after the GROUP BY clause and aggregate functions are executed. It is specifically used to apply restrictions on aggregated results.

Practical example combining WHERE and HAVING:

-- We want only departments with total revenue greater than 100,000 from active sales SELECT department_id, SUM(salary) as folha_pagamento FROM employees WHERE status = 'ACTIVE' -- Filters rows before grouping GROUP BY department_id HAVING SUM(salary) > 100000; -- Filters groups after aggregation

Conclusion

Mastering the correct execution flow of relational queries—combining efficient joins, aggregate functions, clean groupings, and precise filters with HAVING—dramatically elevates the analytical quality of your reports and enterprise queries.