Take your MySQL skills to the next level with our advanced GROUP BY tutorial. Explore real-world examples of GROUP BY in action, including financial analysis, customer behavior tracking, and more. Discover how to efficiently group, filter, and sort data using this powerful SQL clause.
- Advanced MySQL GROUP BY: Examples of Student Tuition and Grade Management Part 1
- Advanced MySQL GROUP BY: Examples of Student Tuition and Grade Management Part 2
Here are 18 advanced examples of MySQL GROUP BY Clause:
GROUP BY with HAVING Clause
Suppose we have a table named orders with columns order_id, customer_id, and total_amount. We want to find out the total amount spent by customers who have made more than one order. We can achieve this using the following query:
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;In this query, we are grouping the data by the customer_id column using the GROUP BY clause. We are also using the HAVING clause to filter the groups and only show those with a count greater than one. The aggregate function COUNT() is used to count the number of orders made by each customer.
GROUP BY with ORDER BY Clause
Suppose we have a table named products with columns product_id, product_name, and unit_price. We want to find out the top 5 most expensive products. We can achieve this using the following query:
SELECT product_name,
unit_price
FROM products
GROUP BY product_id
ORDER BY unit_price DESC
LIMIT 5;In this query, we are grouping the data by the product_id column using the GROUP BY clause. We are also using the ORDER BY clause to sort the groups by unit_price in descending order. Finally, we are using the LIMIT clause to show only the top 5 results.
GROUP BY with JOIN Clause:
Suppose we have two tables named orders and customers with columns order_id, customer_id, order_date, and total_amount in the orders table, and columns customer_id and customer_name in the customers table. We want to find out the total amount spent by each customer along with their name. We can achieve this using the following query:
SELECT customers.customer_name,
SUM(orders.total_amount) AS total_spent
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY orders.customer_id;In this query, we are joining the orders and customers tables using the JOIN clause. We are also grouping the data by the customer_id column in the orders table using the GROUP BY clause. Finally, we are selecting the customer_name column from the customers table and calculating the sum of total_amount for each customer.
GROUP BY with Multiple Columns
Suppose we have a table named orders with columns order_id, customer_id, order_date, and total_amount. We want to find out the total amount spent by each customer for each year. We can achieve this using the following query:
SELECT customer_id,
YEAR(order_date) AS YEAR,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id,
YEAR(order_date);In this query, we are grouping the data by two columns, customer_id and YEAR(order_date), using the GROUP BY clause. We are also using the aggregate function YEAR() to extract the year from the order_date column.
GROUP BY with Alias
Suppose we have a table named orders with columns order_id, customer_id, and total_amount. We want to find out the total amount spent by customers, and we want to give an alias to the aggregated column. We can achieve this using the following query:
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;GROUP BY with COUNT() function
Suppose we have a table named orders with columns order_id, customer_id, and order_date. We want to find out the number of orders made by each customer. We can achieve this using the following query:
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;In this query, we are grouping the data by the customer_id column using the GROUP BY clause. We are also using the COUNT() function to count the number of orders made by each customer.
GROUP BY with SUM() function
Suppose we have a table named orders with columns order_id, customer_id, and total_amount. We want to find out the total amount spent by each customer. We can achieve this using the following query:
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;In this query, we are grouping the data by the customer_id column using the GROUP BY clause. We are also using the SUM() function to calculate the total amount spent by each customer.
GROUP BY with AVG() function
Suppose we have a table named orders with columns order_id, customer_id, and total_amount. We want to find out the average amount spent by each customer. We can achieve this using the following query:
SELECT customer_id, AVG(total_amount) AS avg_spent
FROM orders
GROUP BY customer_id;In this query, we are grouping the data by the customer_id column using the GROUP BY clause. We are also using the AVG() function to calculate the average amount spent by each customer.
GROUP BY with MIN() function
Suppose we have a table named products with columns product_id, product_name, and unit_price. We want to find out the cheapest product in each category. We can achieve this using the following query:
SELECT category_id, MIN(unit_price) AS cheapest_product_price
FROM products
GROUP BY category_id;In this query, we are grouping the data by the category_id column using the GROUP BY clause. We are also using the MIN() function to find the minimum unit_price for each category.
GROUP BY with MAX() function
Suppose we have a table named products with columns product_id, product_name, and unit_price. We want to find out the most expensive product in each category. We can achieve this using the following query:
SELECT category_id, MAX(unit_price) AS expensive_product_price
FROM products
GROUP BY category_id;In this query, we are grouping the data by the category_id column using the GROUP BY clause. We are also using the MAX() function to find the maximum unit_price for each category.
GROUP BY with SUM() and HAVING Clause
Suppose we have a table named orders with columns order_id, customer_id, and total_amount. We want to find out the total amount spent by customers who have spent more than 1000 in total. We can achieve this using the following query:
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;In this query, we are grouping the data by the customer_id column using the GROUP BY clause. We are also using the HAVING clause to filter the groups and only show those with a total amount greater than 1000.
GROUP BY with COUNT() and WHERE Clause
Suppose we have a table named orders with columns order_id, customer_id, and order_date. We want to find out the number of orders made by each customer in the month of January. We can achieve this using the following query:
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE MONTH(order_date) = 1
GROUP BY customer_id;In this query, we are using the WHERE clause to filter the data and only show the orders made in January. We are grouping the data by the customer_id column using the GROUP BY clause. We are also using the COUNT() function to count the number of orders made by each customer.
GROUP BY with CONCAT() function
Suppose we have a table named employees with columns employee_id, first_name, and last_name. We want to find out the number of employees with the same first name and last name. We can achieve this using the following query:
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
COUNT(*) AS name_count
FROM employees
GROUP BY full_name
HAVING name_count > 1;In this query, we are using the CONCAT() function to concatenate the first_name and last_name columns into a single column called full_name. We are grouping the data by the full_name column using the GROUP BY clause. We are also using the HAVING clause to filter the groups and only show those with more than one employee.
GROUP BY with DATE_FORMAT() function
Suppose we have a table named orders with columns order_id, customer_id, and order_date. We want to find out the number of orders made by each customer in each month. We can achieve this using the following query:
SELECT customer_id,
DATE_FORMAT(order_date, '%Y-%m') AS order_month,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id,
order_month;In this query, we are using the DATE_FORMAT() function to format the order_date column into the %Y-%m format, which represents the year and month. We are grouping the data by the customer_id and order_month columns using the GROUP BY clause. We are also using the COUNT() function to count the number of orders made by each customer in each month.
GROUP BY with CASE Statement
Suppose we have a table named products with columns product_id, product_name, and unit_price. We want to group the products into three categories based on their unit_price: “low”, “medium”, and “high”. We can achieve this using the following query:
SELECT CASE
WHEN unit_price < 50 THEN 'low'
WHEN unit_price BETWEEN 50 AND 100 THEN 'medium'
ELSE 'high'
END AS price_category,
COUNT(*) AS product_count
FROM products
GROUP BY price_category;In this query, we are using a CASE statement to categorize the products into three groups based on their unit_price. We are grouping the data by the price_category column using the GROUP BY clause. We are also using the COUNT() function to count the number of products in each category.
GROUP BY with JOIN Clause
Suppose we have two tables named customers and orders, with columns customer_id, customer_name, and order_id, customer_id, order_date, and total_amount, respectively. We want to find out the total amount spent by each customer. We can achieve this using the following query:
SELECT customers.customer_id,
customers.customer_name,
SUM(orders.total_amount) AS total_spent
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;In this query, we are using a JOIN clause to combine the customers and orders tables on the customer_id column. We are selecting the customer_id and customer_name columns from the customers table and the total_amount column from the orders table. We are using the SUM() function to calculate the total amount spent by each customer. We are grouping the data by the customer_id column using the GROUP BY clause.
GROUP BY with Subquery
Suppose we have a table named orders with columns order_id, customer_id, and order_date. We want to find out the number of orders made by each customer, along with the average number of orders made by all customers. We can achieve this using the following query:
SELECT customer_id,
COUNT(*) AS order_count,
(SELECT AVG(order_count)
FROM
(SELECT COUNT(*) AS order_count
FROM orders
GROUP BY customer_id) AS order_counts) AS avg_order_count
FROM orders
GROUP BY customer_id;In this query, we are using a subquery to calculate the average number of orders made by all customers. We are first using a subquery to group the data by customer_id and calculate the number of orders made by each customer. We are then using another subquery to calculate the average of the order counts. We are selecting the customer_id, the order count, and the average order count columns. We are grouping the data by the customer_id column using the GROUP BY clause.
GROUP BY with Rollup
Suppose we have a table named orders with columns order_id, customer_id, and order_date. We want to find out the number of orders made by each customer and the total number of orders made. We can achieve this using the following query:
SELECT customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id WITH ROLLUP;In this query, we are using the WITH ROLLUP clause to include an additional row that shows the total number of orders made by all customers. We are selecting the customer_id and the order count columns. We are grouping the data by the customer_id column using the GROUP BY clause. The additional row shows the total number of orders made by all customers.
