The GROUP BY clause in SQL is used to group rows with the same values in one or more columns into summary rows. It is often used in conjunction with aggregate functions like SUM, COUNT, AVG, and MAX to perform calculations on grouped data. Here are real-time examples and detailed explanations of how to use the GROUP BY clause:
Suppose you have a table called orders with the following columns: order_id, customer_id, order_date, and order_total. You want to find the total order amount for each customer.
sqlCopy code
SELECT customer_id, SUM(order_total) as total_amount
FROM orders
GROUP BY customer_id;
GROUP BY to group the rows in the orders table by the customer_id column.SUM function calculates the total order amount for each group.customer_id with the corresponding total order amount.Suppose you have a table called sales with columns: product_id, category_id, sale_date, and quantity_sold. You want to find the total quantity sold for each product in each category.
sqlCopy code
SELECT category_id, product_id, SUM(quantity_sold) as total_quantity_sold
FROM sales
GROUP BY category_id, product_id;
GROUP BY to group the rows in the sales table by both category_id and product_id.SUM function calculates the total quantity sold for each group (combination of category_id and product_id).category_id and product_id with the corresponding total quantity sold.Suppose you have a table called employees with columns: department_id, employee_id, employee_name, and salary. You want to find the average salary of employees in each department but only include departments with an average salary greater than 50000.
sqlCopy code
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
GROUP BY to group the rows in the employees table by department_id.AVG function calculates the average salary for each department.HAVING clause filters the result set to include only departments with an average salary greater than 50000.