MySQL GROUP BY

Last Updated: 02 July, 2023

In MySQL, we can use the GROUP BY clause to group rows based on a specific condition. The GROUP BY clause is typically used in conjunction with aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on groups of rows.

Here's an example of how we can use the GROUP BY clause with a condition:

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

Here's a more concrete example to illustrate the usage. Let's say we have a table named orders with columns customer_id, order_date, and order_total. You want to calculate the total order amount for each customer who placed an order after a certain date:

 SELECT customer_id, SUM(order_total) AS total_amount
 FROM orders
 WHERE order_date > '2023-01-01'
 GROUP BY customer_id

In this example, the query will calculate the sum of order_total for each customer_id where the order_date is after January 1, 2023. The result will be grouped by customer_id, and the sum will be labeled as total_amount in the result set.

GROUP BY with HAVING clause:

To apply conditions while grouping, we can use the HAVING clause.

Here's an example of how to use GROUP BY with HAVING clause in MySQL:

 SELECT column1, column2, aggregate_function(column3)
 FROM your_table
 GROUP BY column1, column2
 HAVING condition;

Here's a concrete example to illustrate this:

Suppose we have a table named orders with the following columns: order_id, customer_id, and total_amount. We want to find the average order amount for each customer who has placed more than 5 orders.

 SELECT customer_id, AVG(total_amount) as average_amount
 FROM orders
 GROUP BY customer_id
 HAVING COUNT(*) > 5;

In this query, we select the customer_id column and calculate the average of total_amount for each customer. The GROUP BY customer_id groups the rows by the customer ID. The HAVING COUNT(*) > 5 condition filters out customers who have placed fewer than 5 orders.

Note that we can use various conditions within the HAVING clause, such as equality (=, <>), comparison (>, <, >=, <=), or logical operators (AND, OR).