GROUP BY Clause — Learn SQL

Overview

The GROUP BY clause groups rows sharing common values, allowing you to compute aggregate metrics across categories.

Learning Objectives

Group rows by single or multiple columns.
Explain the syntax rules matching aggregates and select scopes.
Understand how database engines execute groupings under the hood.

Detailed Concept Explanation

GROUP BY groups rows with identical values in specified columns into single summary rows.

The Select Rule

Every column in your SELECT list must satisfy one of these two rules:

It is explicitly listed in the GROUP BY clause.
It is wrapped inside an aggregate function (such as SUM(), COUNT(), AVG()).

Failing to follow this rule triggers a compilation error in standard SQL.

Code Examples

SQL

sql

-- INCORRECT: status is not in GROUP BY and lacks an aggregate
SELECT region, status, COUNT(*) 
FROM users 
GROUP BY region;

-- CORRECT: All non-aggregated fields are in the GROUP BY clause
SELECT region, status, COUNT(*) AS count_val
FROM users 
GROUP BY region, status;

Best Practices

Numeric Grouping References: Some databases allow grouping by column positions (e.g., GROUP BY 1, 2 referencing the first two columns in the SELECT list). While this is convenient for ad-hoc queries, write out explicit column names in production pipelines to keep code readable.

Interview Perspective

Danger

Core Error: Column not in GROUP BY

A classic interview query bug is selecting a non-aggregated column that is missing from the GROUP BY clause: SELECT name, dept, SUM(salary) FROM employees GROUP BY dept; This fails because the database cannot match a single name to a department's aggregated salary sum.

Interactive Challenges

Challenge 1: Employees count per department

Select 'department_id' and the total count of employees (alias 'emp_count') from the 'employees' table, grouped by 'department_id'.

Summary

GROUP BY groups rows into category buckets. Ensure all non-aggregated columns in the SELECT clause are included in the GROUP BY list.

Previous LessonMIN & MAX Functions

Next LessonHAVING Clause