Group By Operator In MySQL

In MySQL, The GROUP BY clause is used to group rows based on the values in one or more columns.

It is often used with aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on each group of rows.

The GROUP BY clause in SQL is a powerful feature for aggregating data and retrieving meaningful insights from grouped record sets.

Here are the examples of GROUP BY Operator In MySQL:

Group By Clause Syntax In MySQL:

SELECT column1, aggregate_function(column2)
FROM tableName
GROUP BY column1;

column1: The column by which we want to group the results.
aggregate_function(column2): An aggregate function applied to another column within each group.
tableName: The name of the table from which we want to retrieve data.

GROUP BY Operator In MySQL:

In the below example, it will count the number of `products` associated with the specific `category` and will form a virtual table with columns such as `category` and `num_of_products_category_wise`.

Example:

SELECT category, COUNT(*) AS num_of_products_category_wise
FROM products
GROUP BY category;

The above query groups the `products` by the `category` column and counts the number of `products` in each category.

Grouping with Aggregate Functions In MySQL:

The below query groups the `products` by the `category` column and calculates the average and maximum price for each category.

Example:

SELECT category, AVG(price) AS avg_price, MAX(price) AS max_price
FROM products
GROUP BY category;

Using GROUP BY with Multiple Columns In MySQL:

This query groups the products by both the category and unit columns, providing the count of products for each unique combination of category and unit.

Example:

SELECT category, unit, COUNT(*) AS num_products
FROM products
GROUP BY category, unit;

SUM Column Values Using GROUP BY In MySQL:

This query groups the products based on the `category_code` and calculates the sum of `product_price` column values within each group, by using the SUM function.

Example:

SELECT category_code, SUM(product_price) AS total_product_price
FROM products
GROUP BY category_code;

Using GROUP BY with HAVING Clause In MySQL:

The HAVING clause in GROUP BY Operator is used to filter groups based on aggregate values. It is similar to the WHERE clause but used with aggregate functions.

Example:

SELECT category_code, SUM(product_price) AS total_product_price
FROM products
GROUP BY category_code
HAVING SUM(product_price) > 400;

In the above example, This query groups the products based on the `category_code` and calculates the sum of `product_price` column values within each group, by using the SUM function and we are using HAVING clause to check whether any category_code group record set exceeds our define amount.

<-- Order By

Wildcards -->