70.104
Grouping Data with GROUP BY

Introduction

The GROUP BY statement in SQL is used to arrange identical data into groups. This statement is often used with aggregate functions (COUNT, SUM, MAX, MIN, AVG) to perform an operation on each group of data. The GROUP BY statement is a powerful tool for summarizing or aggregating data, enabling you to quickly extract insights from your datasets.

Use of GROUP BY

• Aggregating Data: To calculate aggregate values like total sales, average price, maximum or minimum value within each group.
• Reporting and Analysis: For creating reports that summarize data in a meaningful way, such as sales per region, average salary by department, or total hours worked per project.
• Data Organization: Helps in organizing data in a structured format, making it easier to understand and analyze.

Basic Syntax

The basic syntax of the GROUP BY statement is as follows:

SELECT column_name(s), AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s);

Examples

1. Counting the Number of Employees in Each Department

   SELECT department, COUNT(employee_id) AS employee_count
   FROM employees
   GROUP BY department;

   This query counts the number of employees in each department by grouping records based on the department column.

2. Calculating Average Salary by Department

   SELECT department, AVG(salary) AS average_salary
   FROM employees
   GROUP BY department;

   Here, the average salary for each department is calculated by grouping employees by their department.

3. Finding Maximum Sale in Each Region

   SELECT region, MAX(sale_amount) AS max_sale
   FROM sales
   GROUP BY region;

   This query finds the maximum sale amount in each region by grouping sales data by the region.

4. Summing Total Sales by Date

   SELECT sale_date, SUM(sale_amount) AS total_sales
   FROM sales
   GROUP BY sale_date;

   This example sums up the total sales for each date, providing a daily sales report.

Points to Remember

• The columns listed in the GROUP BY clause must also be listed in the SELECT statement unless they are used in an aggregate function.
• You can group by multiple columns by separating column names with commas, which allows for more granular grouping.
• The HAVING clause can be used in conjunction with GROUP BY to filter groups based on a condition applied to aggregated data.

The GROUP BY statement, combined with aggregate functions and filtering clauses like WHERE and HAVING, gives SQL a powerful capability to perform complex data analysis and reporting directly from the database.

Tutorial

In this tutorial, Khoury Boston’s Prof. Schedlbauer demonstrates how to organize data into groups using GROUP BY and perform selection and aggregation on groups.

Covers: SELECT, WHERE, GROUP BY, HAVING

---

Files & Resources

All Files for Lesson 70.104

---

References

None.

Errata

Let us know.