MySQL GROUP BY

The MySQL GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, typically for the purpose of performing aggregate functions (such as SUM, COUNT, AVG, etc.) on the grouped data. It is a crucial clause when you want to perform calculations or analysis on groups of data rather than individual rows.

The basic syntax of the GROUP BY clause is as follows:

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

Here’s how it works:

• column1, column2, and so on represent the columns you want to select in your query. These columns can include those you’re grouping by and those on which you want to perform aggregate functions.
• aggregate_function(column) represents an aggregate function (e.g., SUM, COUNT, AVG, MAX, MIN) that you want to apply to one or more columns.
• table_name is the name of the table from which you are selecting data.
• GROUP BY specifies the columns by which you want to group the data.

Here are some examples of how to use the GROUP BY clause in MySQL:

1. Group orders by the customer_id and calculate the total order amount for each customer:

   SELECT customer_id, SUM(total_amount)
   FROM orders
   GROUP BY customer_id;

2. Count the number of products in each category:

   SELECT product_category, COUNT(*) as num_products
   FROM products
   GROUP BY product_category;

3. Calculate the average salary for employees in each department:

   SELECT department, AVG(salary) as avg_salary
   FROM employees
   GROUP BY department;

4. Find the highest and lowest prices for products in each category:

   SELECT product_category, MAX(price) as highest_price, MIN(price) as lowest_price
   FROM products
   GROUP BY product_category;

The GROUP BY clause is essential when you want to summarize data and perform calculations on groups of data rather than individual rows. It allows you to group data based on specific criteria, apply aggregate functions to each group, and generate meaningful summary results for reporting and analysis.