What is the use of the GROUP BY clause?

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows (like “total” or “average”). It is typically used with aggregate functions (e.g., COUNT(), SUM(), AVG(), MAX(), MIN()) to perform calculations on each group of rows.

Key Uses of the GROUP BY Clause:

1. Grouping Rows: It groups the result set by one or more columns, allowing you to perform aggregate calculations on each group.
2. Aggregating Data: Used with aggregate functions to calculate sums, averages, counts, etc., for each group.

Basic Syntax:

SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2;

Explanation:

• column1, column2: The columns you want to group by.
• AGGREGATE_FUNCTION: The function that performs an aggregation, like SUM(), AVG(), etc.
• table_name: The name of the table you’re querying.

Examples:

1. Grouping by a Single Column:
   Suppose you have an employees table, and you want to find out how many employees are in each department.

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

Result:
This will return the count of employees grouped by their respective departments.

2. Grouping by Multiple Columns:
   You can also group by multiple columns to get more granular results. For example, grouping by department and job_title.

SELECT department, job_title, COUNT(*) AS num_employees
FROM employees
GROUP BY department, job_title;

Result:
This will show how many employees are in each department, broken down by job title.

3. Using Aggregate Functions:
   The GROUP BY clause is often used in combination with aggregate functions. For example, calculating the average salary by department:

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

Result:
This will return the average salary of employees in each department.

4. Using HAVING with GROUP BY:
   You can use the HAVING clause to filter groups after they’ve been created by GROUP BY. Unlike WHERE (which filters rows before grouping), HAVING filters groups after the aggregation.

Example to find departments with more than 5 employees:

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Result:
This will return only departments that have more than 5 employees.

Summary of Important Points:

• GROUP BY groups rows by one or more columns.
• It’s typically used with aggregate functions (e.g., COUNT(), SUM(), AVG()) to summarize data.
• HAVING is used to filter the result of groups (unlike WHERE, which filters rows before grouping).
• You can group by multiple columns for more specific aggregation.