Aggregations, GROUP BY, HAVING

When working with databases, we often want more than just raw records—we want to summarize, categorize, and filter data to gain insights. This is where aggregations, the GROUP BY clause, and the HAVING clause play a crucial role.

1. Aggregations – Summarizing the Data

Aggregate functions allow us to perform calculations on a group of rows and return a single result. They help answer questions like: “How many?”, “What’s the average?”, or “What is the total?”

Common aggregate functions:

• COUNT – Counts the number of records.
• SUM – Adds up all the values in a column.
• AVG – Calculates the average value.
• MAX – Finds the highest value.
• MIN – Finds the lowest value.

These functions are most useful in reports, dashboards, and analytics to understand patterns and totals.

2. GROUP BY – Categorizing Your Data

The GROUP BY clause helps organize data into categories or groups so that you can apply aggregate functions per group.

For example, instead of finding the total sales across the entire company, you might want to find sales per region or sales per month. GROUP BY lets you do that by clustering rows that share a common value in a specified column.

This clause is essential when you’re answering questions like:

• “What’s the total revenue per product?”
• “How many users registered per month?”

Without GROUP BY, aggregation would only give a single total, not a breakdown.

3. HAVING – Filtering Grouped Results

Once you’ve grouped your data and performed aggregations, you may want to filter those groups based on some condition. This is where the HAVING clause comes in.

Unlike the WHERE clause (which filters individual rows), HAVING filters groups after aggregation. It lets you apply conditions like:

• “Only show products that sold more than 1,000 units.”
• “List regions where the average order value is greater than ₹500.”

HAVING is used after GROUP BY to refine the output and focus on the most meaningful data.

Summary

• Aggregate functions summarize data (e.g., totals, averages).
• GROUP BY organizes data into categories.
• HAVING filters those categories based on aggregate conditions.

Together, these concepts help you move beyond raw data to generate meaningful insights for decision-making, reporting, and analytics.