• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL GROUP BY

← Previous Next →

The MySQL GROUP BY clause is used to organize rows that have the same values into groups. It is most commonly used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN(). By grouping data, you can generate summaries and reports from large datasets efficiently.

For example, if you have a sales table, you can find the total sales per product or the average order amount per customer using MySQL GROUP BY.

What Is the GROUP BY Clause?

In MySQL, GROUP BY is a SQL clause that groups rows sharing the same values in specified columns. Once grouped, you can perform calculations on each group rather than the entire table.

Key Points:

  • Used with aggregate functions: Aggregate calculations are performed on each group.

  • Can group by one or multiple columns: You can create more complex summaries by combining columns.

  • Helps create reports: Such as totals, averages, counts, or maximum/minimum values per group.

GROUP BY Syntax

The basic syntax for MySQL GROUP BY is:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

Explanation:

  • column1, column2: Columns to group by.

  • aggregate_function(column): Function like COUNT(), SUM(), AVG(), MAX(), MIN().

  • WHERE: Optional filtering before grouping.

  • GROUP BY: Groups rows with the same values.

Example Table

Consider a table called orders:

orders

order_id customer_name product quantity amount
101 Ananya Laptop 2 50000
102 Priya Laptop 1 25000
103 Isha Keyboard 5 5000
104 Riya Laptop 3 75000
105 Ananya Keyboard 2 2000

Basic GROUP BY Example

To calculate total sales per product:

SELECT product, SUM(amount) AS total_sales
FROM orders
GROUP BY product;

Result:

product total_sales
Laptop 150000
Keyboard 7000

Explanation:

  • Rows are grouped by product.

  • SUM(amount) calculates total sales for each product group.

GROUP BY with COUNT()

To find the number of orders for each customer:

SELECT customer_name, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_name;

Result:

customer_name total_orders
Ananya 2
Priya 1
Isha 1
Riya 1

Explanation:

  • COUNT(order_id) counts the number of orders per customer.

  • The result shows total orders for each grouped customer.

GROUP BY with Multiple Columns

You can group by more than one column. For example, total quantity per customer per product:

SELECT customer_name, product, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_name, product;

Result:

customer_name product total_quantity
Ananya Laptop 2
Ananya Keyboard 2
Priya Laptop 1
Isha Keyboard 5
Riya Laptop 3

Explanation:

  • Grouping by customer_name and product creates subgroups.

  • Each combination is calculated separately for total quantity.

GROUP BY with ORDER BY

You can combine GROUP BY with ORDER BY to sort results. For example, to display total sales per product in descending order:

SELECT product, SUM(amount) AS total_sales
FROM orders
GROUP BY product
ORDER BY total_sales DESC;

Result:

product total_sales
Laptop 150000
Keyboard 7000

Explanation:

  • ORDER BY total_sales DESC sorts the grouped results from highest to lowest.

GROUP BY with WHERE Clause

You can filter data before grouping with WHERE. For example, to include only orders above 5000:

SELECT product, SUM(amount) AS total_sales
FROM orders
WHERE amount > 5000
GROUP BY product;

Result:

product total_sales
Laptop 150000

Explanation:

  • WHERE amount > 5000 filters rows before grouping.

  • Only rows matching the condition are included in the SUM calculation.

GROUP BY with HAVING Clause 

While WHERE filters rows before grouping, HAVING filters groups after aggregation.
Example: To show products with total sales above 10,000:

SELECT product, SUM(amount) AS total_sales
FROM orders
GROUP BY product
HAVING SUM(amount) > 10000;

Result:

product total_sales
Laptop 150000

Explanation:

  • Groups with total sales less than 10,000 are excluded.

  • HAVING applies after grouping, unlike WHERE.

Common Mistakes to Avoid

  1. Not including all non-aggregated columns in GROUP BY:
    Columns in SELECT that are not aggregated must appear in the GROUP BY clause.

  2. Using WHERE to filter aggregated results:
    WHERE filters rows before grouping. Use HAVING to filter after aggregation.

  3. Grouping by too many columns unnecessarily:
    This can create very granular groups that may not be meaningful.

  4. Ignoring data types:
    Make sure the grouped columns have consistent types to avoid unexpected results.

Summary of the Tutorial

The MySQL GROUP BY clause is essential for summarizing and reporting data. By combining it with aggregate functions like SUM(), COUNT(), and AVG(), you can calculate totals, counts, averages, and other metrics per group.

  • Use GROUP BY with single or multiple columns to create meaningful groups.

  • Apply WHERE to filter rows before grouping.

  • Use ORDER BY to sort grouped results.

  • Apply HAVING to filter aggregated data after grouping.

Understanding MySQL GROUP BY is a foundational skill for creating analytical queries and generating reports efficiently from your database.

Practice Questions

  1. Write a query to find the total quantity of each product in the orders table using GROUP BY.

  2. Using GROUP BY, display the total sales (SUM(amount)) for each customer.

  3. Write a query to count the number of orders each customer has placed.

  4. Using GROUP BY, show the average quantity ordered per product.

  5. Write a query to find the maximum order amount for each customer.

  6. Using GROUP BY, display the total sales per product where the sales amount is greater than 10,000.

  7. Write a query to show the total number of orders per product per customer using multiple columns in GROUP BY.

  8. Using GROUP BY, find the minimum order amount for each product.

  9. Write a query to display customers who have placed more than 1 order using GROUP BY and HAVING.

  10. Using GROUP BY, list the total quantity of each product and sort the result in descending order of quantity.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top