In SQL, The GROUP BY Operator is used to group rows based on the values from 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.
SELECT column1, aggregate_function(column2), FROM tableName GROUP BY column1;
column1: The column by which you 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 you want to retrieve data.
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.
SELECT category, COUNT(*) AS num_of_products_category_wise FROM products GROUP BY category;
This query groups the products by the category column and counts the number of products in each category.
This query groups the products by the category column and calculates the average and maximum price for each category.
SELECT category, AVG(product_price) AS avg_price, MAX(product_price) AS max_price FROM products GROUP BY category;
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.
SELECT category, unit, COUNT(*) AS num_products FROM products GROUP BY category, unit;
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.
SELECT category_code, SUM(product_price) AS total_product_price FROM products GROUP BY category_code;
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.
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 exceed our define amount.