SQL Aggregation with GROUP BY

From Rows to Summaries: Aggregate Functions

Aggregate functions in SQL operate on a set of rows and return a single, summary value. Imagine you have a list of all sales transactions; an aggregate function could tell you the total value of all sales, or the average sale amount.

The five most common aggregate functions are:

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of a numeric column.
• AVG(): Calculates the average of a numeric column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.

Let's see these functions in action. The following video provides a clear, practical demonstration of how each one is used to analyze an orders table.

To summarize, when used without any grouping, these functions give you a single summary for the entire table.

For example, given a table named Employees with columns Department and Salary:

• SELECT COUNT(*) FROM Employees; would give you the total number of employees.
• SELECT AVG(Salary) FROM Employees; would give you the average salary across all employees.
• SELECT MAX(Salary) FROM Employees; would find the single highest salary in the company.

SELECT COUNT(product_name) AS total_products, AVG(price) AS average_price
FROM Products;

Deeper Insights with GROUP BY

Getting the overall average salary is useful, but what if you want to know the average salary for each department? This requires breaking the data into groups before applying the aggregate function. This is precisely what the GROUP BY clause does.

The GROUP BY clause works through a "Split-Apply-Combine" process:

1. Split: It splits the rows of the table into groups based on the unique values in the column(s) you specify. For example, it would create one group for 'Sales', one for 'Engineering', etc.
2. Apply: It applies the aggregate function (like AVG()) independently to each group.
3. Combine: It combines the results from each group into a final result set, giving you one summary row per group.

The following video tutorial explains this concept very well and also highlights a crucial rule for using GROUP BY.

This idea of grouping and summarizing is similar to creating a Pivot Table in a spreadsheet program like Excel. The following reading material explains this analogy and also shows how to group by more than one column.

So, to find the average salary per department, our query would look like this:

SELECT
  Department,
  AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY
  Department;

This would produce a result like:

Filtering Groups with the HAVING Clause

You now know how to filter individual rows with WHERE and how to group them with GROUP BY. But what if you want to filter the groups themselves? For instance, how would you find only those departments where the average salary is greater than $70,000?

You might instinctively try to use WHERE:

-- This query will cause an error!
SELECT
  Department,
  AVG(Salary) AS AverageSalary
FROM Employees
WHERE
  AVG(Salary) > 70000 -- You can't use an aggregate function in WHERE
GROUP BY
  Department;

This fails because of the logical order of query execution. The WHERE clause is processed before the GROUP BY clause, so at the time WHERE is evaluated, the groups and the AVG(Salary) for those groups don't exist yet.

The solution is the HAVING clause. HAVING is designed specifically to filter groups after they have been created and after the aggregate functions have been calculated.

WHERE filters rows before grouping.
HAVING filters groups after grouping.

The following reading explains this limitation of WHERE and introduces HAVING as the solution.

The correct query to find departments with an average salary above $70,000 is:

SELECT
  Department,
  AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY
  Department
HAVING
  AVG(Salary) > 70000;

SELECT
  Region,
  SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY
  Region
HAVING
  SUM(SaleAmount) > 500000;

The Complete Order of Execution

Understanding the logical order in which SQL clauses are processed is key to writing correct and complex queries. Let's update our mental model from the last lesson to include GROUP BY and HAVING.

The actual order of execution is:

1. FROM: Identifies the source table(s).
2. WHERE: Filters individual rows.
3. GROUP BY: Arranges the filtered rows into groups.
4. HAVING: Filters the groups based on aggregate calculations.
5. SELECT: Selects the final columns and computes expressions.
6. ORDER BY: Sorts the final result set.

This execution order perfectly explains why you can't use an alias from SELECT in a WHERE or HAVING clause (it hasn't been created yet), but you can use it in an ORDER BY clause (it has been created by then).

Conclusion

You've made a significant leap forward today! You've moved beyond simple data retrieval and into the core of data analysis: summarization and aggregation. This is a skill you will use in nearly every data-related task.

Key Takeaways:

• Aggregate Functions (COUNT, SUM, AVG, MIN, MAX) condense many rows into a single summary value.
• The GROUP BY clause is used to split your data into groups, allowing you to apply aggregate functions to each group separately.
• Remember the rule: any non-aggregated column in the SELECT list must also be in the GROUP BY list.
• The HAVING clause is used to filter these groups based on the results of the aggregate functions.
• The logical order of execution (FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY) determines what you can do in each clause.

Preview of the Next Lesson:

So far, all our queries have worked on a single table. However, real-world data is almost always spread across multiple related tables (like Customers, Orders, and Products). In our next lesson, "Write complex SQL queries using JOIN operations and subqueries," you'll learn how to combine data from multiple tables to answer much more complex and interesting questions.