SQL GROUP BY Explained with Examples

What happens to the rows

Picture the table sorted into piles, one pile per dept_id. Each pile becomes a single output row, and aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarise the pile.

Because a pile becomes one row, you cannot select a plain column like name unless it is in GROUP BY: the database would not know which name from the pile to show. PostgreSQL rejects it; MySQL rejects it too under its default ONLY_FULL_GROUP_BY mode.

NULL is a group too

Rows where the grouping column is NULL are not dropped; they form one NULL group. In the example below, Zoya (no department) appears as the NULL group. Where that group sorts differs between databases, so sort by something explicit.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT dept_id, COUNT(*) AS employees, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY employees DESC, avg_salary DESC;

SELECT dept_id, MAX(salary) AS top_salary, MIN(hire_date) AS first_hire
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;

Common mistakes

• Selecting an ungrouped column. Add the column to GROUP BY, wrap it in an aggregate, or use a window function if you need row-level detail next to group totals.
• COUNT(col) vs COUNT(*). COUNT(manager_id) skips employees without a manager. Use COUNT(*) to count rows, COUNT(DISTINCT col) to count unique values.
• Integer averages. Averages and ratios of integers can lose decimals in some databases. Multiply by 1.0 or use ROUND(…, 2) when the question asks for decimals.

Practise GROUP BY

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Multilingual Language Speakers

   Identify users who speak at least two distinct target languages ('English', 'Spanish', 'German') from skill profile records.

   Easy✓ Solved
2. 2

   Daily Active Users (DAU)

   Compute the daily count of unique active users from raw product activity event timestamps.

   Easy✓ Solved
3. 3

   Ad Click-Through Rate (CTR)

   Calculate the percentage CTR per ad campaign rounded to 2 decimals, safely handling campaigns with zero impressions.

   Easy✓ Solved
4. 4

   Play Store: Top Rated App Categories

   Find categories with at least 3 apps rated 4.0 or higher, returning the category, app count, and average rating rounded to 2 decimals.

   Easy✓ Solved
5. 5

   Google Maps: High-Impact Local Guides

   Find verified reviewers who submitted at least 2 photo reviews in San Francisco or Mountain View.

   Easy✓ Solved
6. 6

   Google Drive: External Sensitive File Shares

   Audit sensitive files shared with non-Google email addresses to identify potential data exposure risks.

   Easy✓ Solved

Want a full set? Work through the GROUP BY & Aggregation study plan.

Questions people ask

Can I GROUP BY more than one column?

Yes. GROUP BY dept_id, EXTRACT(YEAR FROM hire_date) makes one group per department and year combination.

Does GROUP BY sort the result?

Not reliably. Add ORDER BY whenever the order matters.

Related concepts

• SQL HAVING vs WHERE: What is the Difference?
• SQL CASE WHEN and Conditional Aggregation
• SQL WHERE, ORDER BY and LIMIT Explained