SQL GROUP BY Keyword

 The GROUP BY keyword in SQL is used to arrange identical data into groups. It is often combined with aggregate functions such as COUNT, SUM, MAX, MIN, or AVG to perform operations on each group of data.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

Key Points

1. The GROUP BY statement groups rows that have the same values in specified columns into summary rows.
2. It is typically used with aggregate functions to produce meaningful summaries of data.
3. Any column in the SELECT clause that is not part of an aggregate function must be included in the GROUP BY clause.

Example 1: Basic Grouping

SELECT department, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department;

• This query counts the number of employees in each department.

Example 2: Filtering with HAVING

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

• This query calculates the average salary for each department but only displays departments where the average salary exceeds 50,000.

Example 3: Grouping Multiple Columns

SELECT department, job_title, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department, job_title;

• This query groups by both department and job_title, providing a count of employees for each combination.

Best Practices

• Use the GROUP BY clause after the WHERE clause but before the ORDER BY clause.
• Ensure all non-aggregated columns in the SELECT clause are listed in the GROUP BY clause to avoid errors.

Would you like help with a specific use case or example?