What is the purpose of the "GROUP BY" clause in SQL?

Answer: GROUP BY groups rows that have the same values in one or more columns so you can compute aggregate results per group.

• Purpose: GROUP BY collects rows that share the same values in the specified column(s) into groups.

• Use with aggregates: Apply aggregate functions such as COUNT, SUM, AVG, MIN, MAX to produce summary values for each group.

• Filtering rules: WHERE filters individual rows before grouping; HAVING filters groups after aggregation.

• Columns in SELECT: Any column in the SELECT list must either be inside an aggregate function or included in the GROUP BY clause (exceptions exist in some SQL dialects for functional dependencies).

• Ordering: GROUP BY does not guarantee row order; use ORDER BY to sort results.

Example 1: Count employees per department

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

Example 2: Average salary per department for departments with average above 70000

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 70000;