Typical Usage of GROUP BY

The GROUP BY clause helps you summarize data by grouping rows with similar values. In this guide, we’ll show you how it works with simple examples.

GROUP BY is an SQL clause that organizes data into groups based on one or more columns, so you can summarize data by calculating things like totals, averages, or counts for each group.

To learn more about using GROUP BY in practice, try our interactive SQL Basics course with 129 hands-on practical exercises to help you master the basics of SQL, including GROUP BY .

Typical Usage of GROUP BY

You’ll typically use GROUP BY to solve problems like:

• Count products by category.
• Find the average price of products in each category.
• Calculate total spending per client.
• Compute the average, minimum, or maximum salary in each department.
• Calculated minimum or maximum grades in each course.
• Count purchases handled by each salesperson.
• Count movies directed by each director.

GROUP BY is typically used with aggregate functions like SUM(), COUNT(), AVG(), MIN(), MAX().

Syntax of GROUP BY

SELECT 
  column1, column2, …,
  aggregate_function(column3), …
FROM table(s)
WHERE …
GROUP BY column1, column2
…

Examples of GROUP BY

Count products by category:

SELECT 
  category,
  COUNT(*)
FROM products
GROUP BY category;

Find the average product price by category:

SELECT 
  category,
  AVG(price)
FROM products
GROUP BY category;

Find the total spending per customer:

SELECT 
  customer_name,
  SUM(total_amount)
FROM purchases
GROUP BY customer_name;

Find the average, minimum, and maximum price per department:

SELECT
  department_name,
  AVG(price),
  MIN(price),
  MAX(price)
FROM employees 
GROUP BY department_name;

How GROUP BY Works

The animation below shows how GROUP BY works logically. It groups rows that have the same values in the column specified in the GROUP BY clause. Then, an aggregate function is applied to each group. In this example, movies are grouped by director, and the COUNT(*) function counts the number of movies in each group.

Summary

The SQL GROUP BY clause is an essential tool for organizing and summarizing data, making it invaluable for analyzing datasets of any size. By grouping rows and applying aggregate functions, you can uncover trends, summarize key metrics, and gain deeper insights. With the examples in this guide, you’re now ready to apply GROUP BY to your own queries and take your SQL skills further.

Want to continue learning? Explore the interactive SQL courses at LearnSQL.com. From mastering the basics to advanced techniques, our courses provide practical exercises and expert guidance to help you succeed in your SQL journey. The All Forever SQL package offers lifetime access to all courses, providing the best value for money. Start learning today!