Lesson 25: Grouping Data with GROUP BY and HAVING

Aggregate functions usually summarize the entire table. The GROUP BY clause divides the rows into groups, allowing you to apply the aggregate function to each group individually.

Using GROUP BY

To see subtotals or summary data for specific categories, you must use GROUP BY.

• Rule: Any column listed in the SELECT statement that is not an aggregate function must be listed in the GROUP BY clause.

Example: Average Salary by Department

sql SELECT Department, AVG(Salary) AS AverageSalary FROM Employees GROUP BY Department; -- Group results by the Department name

Filtering Groups with HAVING

• The WHERE clause filters individual rows before grouping.
• The HAVING clause filters groups after grouping and aggregation has occurred.

Example: Find Departments with more than 5 employees

sql SELECT Department, COUNT(EmployeeID) AS EmployeeCount FROM Employees GROUP BY Department HAVING COUNT(EmployeeID) > 5; -- Filter the groups

The Full Order of Operations

In a standard SQL query, the clauses are executed in a specific order:

1. FROM (Identify source tables)
2. WHERE (Filter individual rows)
3. GROUP BY (Group the filtered rows)
4. HAVING (Filter the resulting groups)
5. SELECT (Determine what columns to display)
6. ORDER BY (Sort the final result set)