GROUP BY Clause

Last Updated: June 12, 2026 • 15 min read

The GROUP BY clause partitions rows into groups that share the same values in specified columns, then computes one aggregate result per group. It transforms a flat table scan into a categorized summary — turning raw transaction data into per-customer totals, individual log entries into per-hour counts, or employee records into per-department statistics.

1. Introduction

GROUP BY partitions rows into groups sharing the same values in specified columns, then computes one aggregate per group. It's the bridge between individual rows and summary statistics. Every non-grouped column in SELECT must be inside an aggregate function (the single-value rule). NULL values are grouped together into one group.

2. Why It Matters

Reporting backbone: Every per-category, per-period, or per-user summary uses GROUP BY.
WHERE vs HAVING: Knowing which filters run before vs after grouping prevents common SQL errors.
JOIN fan-out: One-to-many JOINs inflate aggregates unless you use COUNT(DISTINCT) or pre-aggregate in a CTE.
Multi-column grouping: Grouping by (category, month) creates cross-tabulation summaries essential for analytics.

3. Real-World Analogy

GROUP BY is like sorting mail into labeled bins. The mailroom sorts letters by department (GROUP BY department), then counts how many letters each department received (COUNT). If you sort by department AND day (GROUP BY department, day), each bin holds mail for one department on one specific day. The key rule: you can only report information that's the same for everything in a bin (the group key) or a summary of all items in the bin (an aggregate).

4. How It Works

Clause	Purpose	Aggregates?
WHERE	Filters rows BEFORE grouping	No
GROUP BY	Partitions rows into groups	N/A
HAVING	Filters groups AFTER aggregation	Yes
SELECT	Projects columns and expressions	Yes
ORDER BY	Sorts the final result	Yes

Single-value rule: Every SELECT column must appear in GROUP BY or inside an aggregate function.
PostgreSQL PK exception: GROUP BY a primary key allows SELECT of other columns from the same table.
NULL grouping: NULL values form their own group — all NULL rows collapse into one group.
Expressions allowed: GROUP BY FLOOR(age / 10) * 10 groups by age brackets.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

WHERE for aggregate filters: WHERE COUNT(*) > 5 is invalid — WHERE runs before grouping. Use HAVING.
JOIN fan-out: One-to-many JOINs inflate COUNT and SUM. Use COUNT(DISTINCT id) or pre-aggregate in a CTE.
Non-grouped columns in SELECT: PostgreSQL errors with "column must appear in GROUP BY or be used in an aggregate function" unless it's functionally dependent on the PK.

Interview Insight

"Find departments where average salary exceeds the company average." Requires a subquery or CTE for the company-wide AVG, then GROUP BY department with HAVING AVG(salary) > company_avg.

Common Pitfall

WHERE cannot filter on aggregates. Use HAVING instead: HAVING COUNT(*) > 5. Remember: "WHERE filters rows, HAVING filters groups."

9. Quick Quiz

Q1: Can you use a column alias in GROUP BY?

A) Yes, always B) No, never C) Yes in PostgreSQL, not in all databases

Answer: C — PostgreSQL allows GROUP BY alias. Standard SQL and some databases don't.

Q2: What happens to NULL values in GROUP BY?

A) Excluded B) Grouped together C) Each NULL is its own group

Answer: B — All NULL values in the GROUP BY column form a single group.

10. Scenario-Based Challenge

Customer Tiering with Aggregates

Group customers into tiers (Bronze <$1K, Silver $1-5K, Gold $5-10K, Platinum >$10K) based on lifetime spending. Count customers per tier with average order value. Use a CASE expression in GROUP BY or a CTE with computed tiers. Bonus: include customers who have never ordered as a "Prospect" tier using COALESCE.

11. Debugging Exercise

This query returns inflated order counts. Why?

12. Interview Questions

Q: What's the difference between WHERE and HAVING?

A: WHERE filters individual rows before GROUP BY. HAVING filters groups after aggregation. WHERE cannot contain aggregate functions; HAVING can. Put non-aggregate filters in WHERE for better performance.

Q: How does JOIN fan-out affect aggregates?

A: One-to-many JOINs duplicate the "one" side rows, inflating COUNT and SUM. Fix with COUNT(DISTINCT id) or pre-aggregate in a CTE before joining.

Q: Can you GROUP BY an expression?

A: Yes. GROUP BY DATE_TRUNC('month', created_at) groups by month. GROUP BY FLOOR(age/10)*10 groups by age bracket. Any deterministic expression is valid.

13. Production Considerations

Pre-aggregate with CTEs: When joining fact tables, aggregate first in a CTE, then JOIN. This prevents fan-out and often improves performance.
Index support: B-tree indexes on GROUP BY columns enable GroupAggregate (sorted scan) instead of HashAggregate — faster for pre-sorted data.
Partial aggregates: Use WHERE to filter early and reduce rows before grouping. Push non-aggregate conditions to WHERE, not HAVING.
GROUPING SETS / ROLLUP / CUBE: For multi-level summaries in a single query, use GROUPING SETS instead of multiple queries with UNION.