MySQL - Group By Queries

GROUP BY is a clause used to group rows that have the same values into summary rows, like a summary report. It is often used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Example 1:

Suppose you have a table named "orders" that contains information about customer orders, including the customer ID, order date, and order total. If you want to create a report that lists the total orders for each customer, you can use GROUP BY to group the orders by customer ID and calculate the total order amount for each customer.

SELECT customer_id, SUM(order_total) as total_orders

FROM orders

GROUP BY customer_id;

In this example, we're selecting the "customer_id" column from the "orders" table and calculating the total order amount using the SUM function. We're using GROUP BY to group the orders by customer ID so that the SUM function calculates the total order amount for each customer.

The result of this query will be a table that lists the total orders for each customer, with each row representing a different customer. The "customer_id" column contains the unique customer IDs, and the "total_orders" column contains the total order amount for each customer.

It's important to note that when using GROUP BY, the SELECT statement can only include columns that are part of the GROUP BY clause or are used in aggregate functions like COUNT, SUM, AVG, MIN, and MAX. Additionally, the columns in the GROUP BY clause determine the order in which the rows are grouped.

Example 2:

Suppose you have a table named "sales" that contains information about daily sales, including the date, product name, and sales amount. If you want to create a report that groups the sales by week and calculates the total sales for each week, you can use GROUP BY to group the sales by a date range:

SELECT DATE_FORMAT(date, '%Y-%m-%d') AS week_start_date, SUM(sales_amount) AS total_sales

FROM sales

GROUP BY WEEK(date);

In this example, we're selecting the week start date using the DATE_FORMAT function and calculating the total sales using the SUM function. We're using GROUP BY to group the sales by week, which is determined by the WEEK function.

Example 3:

Suppose you have a table named "employees" that contains information about employee salaries, including the department name, job title, and salary. If you want to create a report that groups the salaries by department and job title, and calculates the average salary for each group, you can use GROUP BY to group the salaries by multiple columns:

SELECT department_name, job_title, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_name, job_title;

In this example, we're selecting the department name, job title, and average salary using the AVG function. We're using GROUP BY to group the salaries by both the department name and job title, which creates a unique group for each combination of department and job title.

Example 4:

Suppose you have a table named "orders" that contains information about customer orders, including the customer ID, order date, and order total. If you want to create a report that groups the orders by customer ID and calculates the total order amount for each customer, but only shows customers with total orders greater than ₹1000, you can use GROUP BY with a HAVING clause:

SELECT customer_id, SUM(order_total) AS total_orders

FROM orders

GROUP BY customer_id

HAVING total_orders > 1000;

In this example, we're selecting the customer ID and total order amount using the SUM function. We're using GROUP BY to group the orders by customer ID and HAVING to filter out customers with total orders less than or equal to $1000. The result of this query will be a table that lists the total orders for each customer with total orders greater than ₹1000.

Example 5:

Suppose you have a table named "students" that contains information about student test scores, including the student name, test name, and score. If you want to create a report that groups the test scores by student name and test name, and shows only the tests where the student's average score is above 80, you can use GROUP BY with a HAVING clause:

SELECT student_name, test_name, AVG(score) AS average_score

FROM students

GROUP BY student_name, test_name

HAVING AVG(score) > 80;

In this example, we're selecting the student name, test name, and average score using the AVG function. We're using GROUP BY to group the test scores by both the student name and test name. We're then using the HAVING clause to filter out tests where the student's average score is less than or equal to 80. The result of this query will be a table that lists the test scores for each student and test where the student's average score is above 80.