SQL GROUP BY Clause: Complete Guide with Examples and Optimization Tips

The `GROUP BY` clause is one of the most powerful and frequently used features in SQL. It allows you to group rows that have the same values in specified columns and then perform aggregate calculations on each group — such as counting, summing, averaging, finding minimums or maximums.

In this article, we’ll cover what `GROUP BY` is, how it works, syntax, common patterns, advanced usage with `HAVING`, and important performance considerations.

1. What is the GROUP BY Clause?

`GROUP BY` divides the rows returned by a query into groups based on one or more columns. For each group, you can apply aggregate functions like `COUNT()`, `SUM()`, `AVG()`, `MIN()`, `MAX()`, etc.

Without `GROUP BY`, aggregate functions would return a single value for the entire result set. With `GROUP BY`, you get one row per group.

Simple Analogy: Imagine a sales report. Instead of showing every single order, `GROUP BY` lets you summarize total sales by customer, by month, or by region.

2. Basic Syntax

```sql

SELECT

column1,

column2,

aggregate_function(column3)

FROM table_name

WHERE condition

GROUP BY column1, column2

HAVING condition_on_aggregate

ORDER BY aggregate_function(column3) DESC;

```

Important Rules:

- All non-aggregated columns in the `SELECT` list must appear in the `GROUP BY` clause (or be functionally dependent on it).

- You can group by multiple columns.

- `WHERE` filters rows before grouping.

- `HAVING` filters groups after grouping.

3. Basic Examples

Example 1: Simple Grouping

```sql

-- Count employees per department

SELECT

department,

COUNT() AS employee_count

FROM employees

GROUP BY department;

```

Example 2: Multiple Columns + Aggregates

```sql

SELECT

department,

job_title,

COUNT() AS headcount,

AVG(salary) AS avg_salary,

SUM(salary) AS total_salary,

MAX(salary) AS highest_salary

FROM employees

GROUP BY department, job_title

ORDER BY avg_salary DESC;

```

Example 3: Grouping by Date (Common Pattern)

```sql

SELECT

DATE_TRUNC('month', order_date) AS order_month, -- PostgreSQL

-- DATE_FORMAT(order_date, '%Y-%m') AS order_month, -- MySQL

COUNT() AS order_count,

SUM(total_amount) AS monthly_revenue

FROM orders

GROUP BY DATE_TRUNC('month', order_date)

ORDER BY order_month;

```

4. GROUP BY with HAVING Clause

`HAVING` is used to filter groups based on aggregate conditions (you cannot use aggregates in `WHERE`).

```sql

-- Departments with more than 10 employees and average salary > 80,000

SELECT

department,

COUNT() AS employee_count,

AVG(salary) AS avg_salary

FROM employees

GROUP BY department

HAVING COUNT() > 10

AND AVG(salary) > 80000;

```

5. Advanced GROUP BY Patterns

Using GROUP BY with JOINs

```sql

SELECT

c.name AS customer_name,

COUNT(o.order_id) AS total_orders,

SUM(o.total_amount) AS total_spent

FROM customers c

JOIN orders o ON c.id = o.customer_id

GROUP BY c.id, c.name

HAVING SUM(o.total_amount) > 5000;

```

GROUP BY with CTEs (Recommended for Readability)

```sql

WITH monthly_sales AS (

SELECT

DATE_TRUNC('month', order_date) AS order_month,

SUM(total_amount) AS revenue

FROM orders

GROUP BY DATE_TRUNC('month', order_date)

)

SELECT

order_month,

revenue,

LAG(revenue) OVER (ORDER BY order_month) AS previous_month_revenue

FROM monthly_sales

ORDER BY order_month;

```

ROLLUP and CUBE (for Subtotal Reports)

```sql

-- PostgreSQL / SQL Server / Oracle

SELECT

department,

job_title,

COUNT() AS headcount

FROM employees

GROUP BY ROLLUP(department, job_title);

```

This produces subtotals for department and a grand total.

6. GROUP BY Performance & Optimization Tips

`GROUP BY` can become expensive on large tables. Here’s how to optimize it:

1. Index the GROUP BY Columns

- Create indexes on columns used in `GROUP BY` (especially the leftmost columns in composite indexes).

- This often allows the database to use an Index Scan or Stream Aggregate instead of Hash Aggregate.

2. Filter Early with WHERE

- Reduce rows before grouping using `WHERE` instead of `HAVING` whenever possible.

3. Be Careful with SELECT

- Only select necessary columns. Extra columns increase memory usage during grouping.

4. Watch Execution Plans

- Look for Hash Aggregate (memory-intensive) vs Stream Aggregate (better when data is pre-sorted by index).

- High memory grants or spills to tempdb/disk are warning signs.

5. Consider Materialized Views

- For frequently run heavy `GROUP BY` queries (daily reports, dashboards), consider a materialized view.

6. Use Approximate Aggregates (when acceptable)

- PostgreSQL: `approx_count_distinct()` or HyperLogLog extensions for very large datasets.

7. Common GROUP BY Mistakes to Avoid

- Forgetting to include all non-aggregated columns in `GROUP BY` (causes errors in strict mode).

- Using `HAVING` when `WHERE` would suffice (slower).

- Grouping by expressions instead of raw columns (prevents index usage).

- Bad: `GROUP BY YEAR(order_date)`

- Good: `GROUP BY DATE_TRUNC('year', order_date)` or create a computed column/index.

- Applying `ORDER BY` on aggregates without need (adds extra sort cost).

8. Comparison: GROUP BY vs Other Techniques

| Scenario | Recommended Approach | Reason |

|---------------------------------|----------------------------------|-------------------------------------|

| Simple aggregates | GROUP BY | Straightforward |

| Very complex logic | GROUP BY inside CTE | Better readability |

| Frequently queried summaries | Materialized View | Pre-computed performance |

| Running totals / trends | Window Functions + GROUP BY | More powerful than GROUP BY alone |

| Very large datasets | Approximate functions + indexing | Reduces memory and time |

Conclusion

The `GROUP BY` clause is essential for data summarization and reporting. When used correctly with proper indexing, early filtering, and good query structure, it delivers excellent performance even on large tables.

Master `GROUP BY` together with indexes, execution plans, and CTEs to build fast, maintainable analytical queries.

Pro Tip: Always check the execution plan of your `GROUP BY` queries. If you see expensive Hash Aggregates on large tables, consider adding a composite index on the grouping columns.