SQL GROUP BY and Aggregate Functions

Lesson 7: SQL GROUP BY and Aggregate Functions

Objective: In this lesson, you will learn how to group data in SQL using the GROUP BY clause and apply aggregate functions like COUNT, SUM, AVG, MIN, and MAX to perform calculations on your data.

1. What is GROUP BY?

The GROUP BY statement in SQL is used to group rows that have the same values into summary rows, like “total salary” for each department. It is often used with aggregate functions to perform calculations on each group of rows.

Syntax of GROUP BY:

SELECT column1, column2, aggregate_function(column3)
FROM table
WHERE condition
GROUP BY column1, column2;

2. Aggregate Functions in SQL

SQL provides several aggregate functions that allow you to perform calculations on data in your tables. Some common aggregate functions are:

• COUNT(): Returns the number of rows that match the specified condition.
• SUM(): Returns the sum of the values in a numeric column.
• AVG(): Returns the average value of a numeric column.
• MIN(): Returns the minimum value in a column.
• MAX(): Returns the maximum value in a column.

3. Using GROUP BY with Aggregate Functions

When you use the GROUP BY clause with aggregate functions, you can calculate summary information for each group. Let’s look at an example using a Sales table.

Example: Sales Data

Sales Table:

SalesID | EmployeeID | Amount  | Date
1       | 101        | 1500    | 2023-01-01
2       | 102        | 2000    | 2023-01-02
3       | 101        | 1200    | 2023-01-03
4       | 103        | 500     | 2023-01-04
5       | 102        | 1800    | 2023-01-05

Objective: Find the total sales for each employee.

Query: To calculate the total sales for each employee, use the GROUP BY clause and the SUM() aggregate function:

SELECT EmployeeID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY EmployeeID;

Output:

EmployeeID | TotalSales
101        | 2700
102        | 3800
103        | 500

4. Filtering Groups with HAVING

Sometimes, you need to filter groups after applying GROUP BY. The HAVING clause is used to filter records after the grouping is done, similar to how WHERE works for filtering individual rows.

Example:

Objective: Find the employees who have total sales greater than 2000.

Query: Use the HAVING clause to filter the groups:

SELECT EmployeeID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY EmployeeID
HAVING SUM(Amount) > 2000;

Output:

EmployeeID | TotalSales
102        | 3800
101        | 2700

5. Using COUNT() for Grouping

The COUNT() function is used to count the number of rows that match the condition. You can use it to find how many items exist in each group.

Example:

Objective: Find the number of sales transactions for each employee.

Query: Use the COUNT() function to count the number of sales for each employee:

SELECT EmployeeID, COUNT(SalesID) AS NumberOfSales
FROM Sales
GROUP BY EmployeeID;

Output:

EmployeeID | NumberOfSales
101        | 2
102        | 2
103        | 1

6. Using AVG(), MIN(), and MAX()

In addition to COUNT() and SUM(), you can also use AVG(), MIN(), and MAX() to calculate the average, minimum, and maximum values of a column for each group.

Example:

Objective: Find the average, minimum, and maximum sales for each employee.

Query: Use the AVG(), MIN(), and MAX() functions:

SELECT EmployeeID, AVG(Amount) AS AverageSales, MIN(Amount) AS MinSales, MAX(Amount) AS MaxSales
FROM Sales
GROUP BY EmployeeID;

Output:

EmployeeID | AverageSales | MinSales | MaxSales
101        | 1350         | 1200     | 1500
102        | 1900         | 1800     | 2000
103        | 500          | 500      | 500

7. Practice Exercises

Try these exercises to reinforce your understanding of GROUP BY and aggregate functions:

• Write a query to find the total sales for each employee using SUM() and GROUP BY.
• Write a query to find the average sales for each employee.
• Write a query to find the number of transactions for each employee using COUNT().
• Write a query to find the employee with the highest total sales.

8. Conclusion

In this lesson, you learned how to use the GROUP BY clause in SQL to group data and apply aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(). These functions are essential for summarizing and analyzing data in SQL.

Key Takeaways:

• The GROUP BY clause is used to group rows based on specified columns.
• Aggregate functions allow you to perform calculations on grouped data.
• Use HAVING to filter groups after applying GROUP BY.
• Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().