The Project | About Us | Contribute | Donations | License

HOME

In this section, we will explore how to use the GROUP BY clause in SQL to aggregate data. Aggregation is a powerful feature that allows you to summarize and analyze data in meaningful ways. The GROUP BY clause is used in conjunction with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to group rows that have the same values in specified columns into summary rows.

Key Concepts

GROUP BY Clause: Used to group rows that have the same values in specified columns.
Aggregate Functions: Functions that perform a calculation on a set of values and return a single value. Common aggregate functions include:
- COUNT(): Counts the number of rows.
- SUM(): Calculates the total sum of a numeric column.
- AVG(): Calculates the average value of a numeric column.
- MAX(): Finds the maximum value in a column.
- MIN(): Finds the minimum value in a column.

Basic Syntax

SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2;

column1, column2: Columns to group by.
AGGREGATE_FUNCTION(column3): The aggregate function applied to column3.

Practical Example

Let's consider a table named sales with the following structure:

sale_id	product_id	quantity	price	sale_date
1	101	2	10.00	2023-01-01
2	102	1	20.00	2023-01-02
3	101	3	10.00	2023-01-03
4	103	1	30.00	2023-01-04
5	102	2	20.00	2023-01-05

Example 1: Total Quantity Sold per Product

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

Explanation:

product_id: The column to group by.
SUM(quantity) AS total_quantity: Calculates the total quantity sold for each product.

Result:

product_id	total_quantity
101	5
102	3
103	1

Example 2: Average Price per Product

SELECT product_id, AVG(price) AS average_price
FROM sales
GROUP BY product_id;

Explanation:

product_id: The column to group by.
AVG(price) AS average_price: Calculates the average price for each product.

Result:

product_id	average_price
101	10.00
102	20.00
103	30.00

Practical Exercises

Exercise 1: Count the Number of Sales per Product

Task: Write a query to count the number of sales for each product.

SELECT product_id, COUNT(sale_id) AS number_of_sales
FROM sales
GROUP BY product_id;

Expected Result:

product_id	number_of_sales
101	2
102	2
103	1

Exercise 2: Find the Maximum Quantity Sold in a Single Sale per Product

Task: Write a query to find the maximum quantity sold in a single sale for each product.

SELECT product_id, MAX(quantity) AS max_quantity
FROM sales
GROUP BY product_id;

Expected Result:

product_id	max_quantity
101	3
102	2
103	1

Common Mistakes and Tips

Grouping by Non-Aggregated Columns: Ensure that all columns in the SELECT statement that are not part of an aggregate function are included in the GROUP BY clause.
Using Aliases: Use aliases for aggregate functions to make the result set more readable.
NULL Values: Be aware of how NULL values are handled in aggregate functions. For example, COUNT(column) does not count NULL values, but COUNT(*) does.

Summary

In this section, we learned how to use the GROUP BY clause to aggregate data in SQL. We covered the basic syntax, explored practical examples, and provided exercises to reinforce the concepts. Understanding how to group and aggregate data is essential for summarizing and analyzing large datasets effectively. In the next section, we will delve into the HAVING clause, which allows us to filter groups based on aggregate values.

Aggregating Data with GROUP BY

Key Concepts

Basic Syntax

Practical Example

Example 1: Total Quantity Sold per Product

Example 2: Average Price per Product

Practical Exercises

Exercise 1: Count the Number of Sales per Product

Exercise 2: Find the Maximum Quantity Sold in a Single Sale per Product

Common Mistakes and Tips

Summary

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project