Grouping Rows with GROUP BY

GROUP BY is an SQL clause that combines rows with matching values into groups. You can then calculate a summary for each group using aggregate functions.

Imagine an online store with thousands of orders. You may want to know:

- How many orders each customer has placed.
- How much revenue each product category generated.
- What the average salary is in each department.
- How many employees work in each city.
- How many orders each customer placed in each year.

Without GROUP BY, an aggregate query can calculate one overall result. With GROUP BY, it can calculate a separate result for each category.

For example, if three orders belong to the Electronics category and two belong to the Books category, grouping by category produces two result rows rather than five individual order rows.

This lesson uses PostgreSQL syntax.

The basic syntax is:

SELECT grouping_column, aggregate_function(column)
FROM table_name
GROUP BY grouping_column;

The query works conceptually as follows:

1. FROM identifies the source table.
2. GROUP BY forms groups based on matching grouping values.
3. Aggregate functions calculate results for each group.
4. SELECT returns the requested grouping values and calculated results.

Example:

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

This produces one result row per distinct department value.

The table and columns used in this example must exist in your database.

Consider this sample orders data:

| order_id | category | amount |
|---:|---|---:|
| 1 | Electronics | 500 |
| 2 | Books | 100 |
| 3 | Electronics | 300 |
| 4 | Books | 200 |
| 5 | Electronics | 200 |

There are five individual rows.

Now execute:

SELECT category, COUNT(*) AS order_count
FROM orders
GROUP BY category;

PostgreSQL combines rows with the same category value.

Electronics has three rows.
Books has two rows.

The grouped output has two rows, one for each category.

Remember: GROUP BY does not simply remove duplicate rows from the original table. It creates groups so that aggregate calculations can be performed on each group.

COUNT is one of the most common aggregate functions used with GROUP BY.

COUNT(*) counts all rows in each group.

COUNT(column_name) counts rows where that column is not NULL.

COUNT(DISTINCT column_name) counts distinct non-NULL values in the column within each group.

Example:

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

If a department contains eight employee rows, COUNT(*) returns 8 for that department.

Use COUNT(*) when the question is about the number of records.

SUM calculates the total of non-NULL numeric values in each group.

It is useful for:
- Total sales by category.
- Total salary by department.
- Total payment amount by customer.
- Total units sold by product.

Example:

SELECT category, SUM(amount) AS total_sales
FROM orders
GROUP BY category;

SUM ignores NULL input values. If every amount in a group is NULL, SUM returns NULL rather than zero.

AVG calculates the average of non-NULL numeric values in each group.

The average is calculated as the sum of the non-NULL values divided by the number of non-NULL values.

Example:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

If salaries are 30000, 40000, and 50000, the average is 40000.

AVG does not treat NULL as zero. Rows with NULL salary values are excluded from the average calculation.

MIN returns the smallest non-NULL value in a group. MAX returns the largest non-NULL value in a group.

They are useful for:
- Lowest and highest salary per department.
- Earliest and latest order timestamp per customer.
- Minimum and maximum product price per category.
- First and last recorded measurements.

Example:

SELECT
department,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

MIN and MAX can work with numeric, date, timestamp, and other comparable values. Their meaning depends on the data type.

A single GROUP BY query can use several aggregate functions.

For example, a sales report may need:
- The number of orders.
- Total revenue.
- Average order value.
- The smallest order.
- The largest order.

You do not need a separate query for every calculation when the source and grouping are the same.

You can group by more than one column.

Syntax:

SELECT column_a, column_b, COUNT(*)
FROM table_name
GROUP BY column_a, column_b;

Each distinct combination of column_a and column_b becomes a separate group.

For example, grouping by category and status produces groups such as:

- Electronics, Completed
- Electronics, Pending
- Books, Completed
- Books, Pending

The same category can appear multiple times because each status creates a separate group within that category.

Suppose the source rows are:

| category | status | amount |
|---|---|---:|
| Books | Completed | 100 |
| Books | Completed | 200 |
| Books | Pending | 150 |
| Electronics | Completed | 500 |
| Electronics | Pending | 300 |

When you group only by category, there are two groups: Books and Electronics.

When you group by category and status, there are four groups:

1. Books, Completed
2. Books, Pending
3. Electronics, Completed
4. Electronics, Pending

Each group contains only rows that match both grouping values.

The number of result rows depends on how many distinct combinations exist in the actual data, not on the number of columns in the query.

Every expression in the SELECT list must make sense for each output group.

In a grouped query, a selected expression generally must be:
- Included in GROUP BY.
- An aggregate expression such as COUNT(*), SUM(amount), or AVG(salary).
- Otherwise valid under the database's grouping rules, such as a column functionally dependent on a grouped primary key in supported cases.

Consider this query:

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

This is generally invalid in PostgreSQL because a department can contain multiple employees. SQL cannot choose one employee_name that represents the whole department.

Correct version when you want a department summary:

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

Correct version when you want individual employee rows:

SELECT department, employee_name
FROM employees;

Do not add a column to GROUP BY just to silence an error unless grouping by that column matches the question you actually want to answer.

When a grouping column contains NULL, PostgreSQL groups rows with NULL in that column together.

For example, if three employees have no recorded department, grouping by department produces one group for those three rows.

This behavior differs from ordinary equality comparisons: NULL = NULL does not evaluate to TRUE, but GROUP BY treats NULL grouping values as belonging to the same group.

Example:

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

A row with a NULL department may appear with a NULL department value in the output.

Do not confuse a NULL grouping value with the text 'NULL' or an empty string. They are different values.

WHERE filters individual rows before GROUP BY creates groups.

Use WHERE when you want to decide which source rows are included in the calculations.

Example:

SELECT category, SUM(amount)
FROM orders
WHERE status = 'Completed'
GROUP BY category;

Only completed orders contribute to the grouped totals.

Conceptual processing order:

FROM → WHERE → GROUP BY → aggregate calculations → SELECT

WHERE does not filter groups based on the calculated SUM or COUNT. For that requirement, use HAVING.

HAVING filters groups after grouping and aggregate calculations.

Use WHERE to filter individual rows before grouping.

Use HAVING to filter groups based on aggregate results.

Example requirement:
Show only categories with more than two orders.

Query:

SELECT category, COUNT(*) AS order_count
FROM orders
GROUP BY category
HAVING COUNT(*) > 2;

The aggregate condition belongs in HAVING because it depends on the count for each group.

Remember:
- WHERE filters rows.
- HAVING filters groups.

HAVING is introduced here because it is closely related to GROUP BY. It can be studied in greater detail in a dedicated lesson.

Consider the requirement: "Show departments with more than five employees."

This query is not valid PostgreSQL:

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

The problem is that WHERE filters source rows before the groups and their counts have been calculated.

Use HAVING instead:

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

Comparison:

| WHERE | HAVING |
|---|---|
| Filters individual source rows | Filters grouped results |
| Runs before grouping in logical query processing | Runs after grouping |
| Commonly uses ordinary column conditions | Commonly uses aggregate conditions |
| Can reduce the rows entering aggregation | Can reduce the groups returned |

You can use both clauses in the same query.

You can group by an expression rather than only by a stored column.

For example, you can group employees by the first character of a name or group timestamps by month.

When grouping by a calculated expression, ensure the selected expression and grouping expression correspond correctly.

For date-based reporting, DATE_TRUNC is often useful because it groups timestamps by a meaningful calendar period.

Sometimes the business requirement involves a calculated category rather than an existing column.

For example, an application may classify orders as Small, Medium, or Large according to their amount.

A CASE expression can create these categories, and GROUP BY can aggregate rows within each category.

Use the same CASE expression in SELECT and GROUP BY for a straightforward, explicit PostgreSQL query.

GROUP BY is often used with joins to produce reports across related tables.

Suppose customers and orders are stored in separate tables. You can join them and calculate the total order amount per customer.

Example assumptions:
- customers has customer_id and customer_name.
- orders has order_id, customer_id, and amount.

A LEFT JOIN preserves customers even when they have no matching orders.

When counting orders in a LEFT JOIN query, use COUNT(orders.order_id) rather than COUNT(*). COUNT(*) counts the preserved customer row even when no order exists.

GROUP BY does not automatically correct duplicate records caused by an incorrect join.

Suppose an order is joined to multiple matching rows in another table. The join may multiply that order's amount before SUM runs. The resulting total can be inflated.

Before trusting an aggregate result, verify:
- Whether each join matches the intended number of rows.
- Whether the relationship is one-to-one, one-to-many, or many-to-many.
- Whether the aggregate should count source rows, distinct entities, or another unit.
- Whether the join duplicates the values being summed.

COUNT(DISTINCT entity_id) can be appropriate when the question specifically asks for distinct entities. However, SUM(DISTINCT amount) is not a general fix for duplicated joins because different legitimate orders can have the same amount.

The right solution is to correct the join or aggregate at the appropriate level.

GROUP BY does not guarantee that result rows appear in a particular order.

For example:

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

The database may return groups in different orders depending on the execution plan and data.

If order matters, specify ORDER BY:

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

You can also sort by an aggregate:

ORDER BY COUNT(*) DESC

or by its output alias in PostgreSQL:

ORDER BY employee_count DESC

Use a secondary sort key if you want deterministic ordering when the main sort values are tied.

Grouping can require significant work when the source table contains many rows.

PostgreSQL may use strategies such as:
- Hash aggregation.
- Sort-based aggregation.
- Parallel aggregation when supported and beneficial.

The selected plan depends on factors including table size, data distribution, memory, indexes, statistics, and query structure.

Practical guidelines:

1. Filter unnecessary source rows before grouping when possible.
2. Select only the grouping columns and aggregates you need.
3. Ensure joins do not multiply rows unexpectedly.
4. Avoid assuming an index always makes GROUP BY faster.
5. Inspect EXPLAIN when a query performs poorly.
6. Use EXPLAIN ANALYZE when you need actual execution measurements and it is safe to execute the query.

An index can help some grouping workloads, but PostgreSQL may choose another plan when that is cheaper.

| Requirement | Query pattern |
|---|---|
| Count rows per category | GROUP BY category with COUNT(*) |
| Total sales per category | GROUP BY category with SUM(amount) |
| Average salary per department | GROUP BY department with AVG(salary) |
| Group by multiple categories | GROUP BY category, status |
| Filter rows before grouping | WHERE condition |
| Filter groups by total | HAVING SUM(amount) > threshold |
| Group timestamps by month | GROUP BY DATE_TRUNC('month', timestamp_column) |
| Keep customers with no orders | LEFT JOIN with GROUP BY |
| Count matching orders after a LEFT JOIN | COUNT(non_null_order_id) |
| Sort grouped results | ORDER BY grouping_column or aggregate |

Always start with the business question. Decide what one output row should represent, then choose the grouping columns and aggregate functions that produce that result.

Use this sample data:

| category | amount |
|---|---:|
| Books | 100 |
| Books | 200 |
| Electronics | 500 |
| Electronics | 300 |
| Electronics | 200 |

Question 1:
SELECT category, COUNT(*)
FROM orders
GROUP BY category;

Question 2:
SELECT category, SUM(amount)
FROM orders
GROUP BY category;

Question 3:
SELECT category, AVG(amount)
FROM orders
GROUP BY category;

Question 4:
SELECT category, MIN(amount), MAX(amount)
FROM orders
GROUP BY category;

Question 5:
What changes when you group by category and status instead of category alone?

Question 6:
Why is SELECT category, amount, COUNT(*) FROM orders GROUP BY category generally invalid?

Question 7:
What is the difference between WHERE and HAVING?

Question 8:
If the category column contains NULL values, how does GROUP BY handle them?

Question 9:
Does GROUP BY guarantee alphabetical output?

Question 10:
Why might a SUM become too large after joining two tables?

1. Count by category:

Books: 2
Electronics: 3

COUNT(*) counts all source rows in each group.

2. Total amount by category:

Books: 300
Electronics: 1000

SUM adds the amounts in each group.

3. Average amount by category:

Books: 150
Electronics: approximately 333.33

AVG calculates the mean of the non-NULL amounts.

4. Minimum and maximum:

Books: minimum 100, maximum 200
Electronics: minimum 200, maximum 500

5. Grouping by category and status creates one group for each distinct combination of those values.

6. The amount column can have different values within one category. It must be grouped, aggregated, or otherwise valid under PostgreSQL's grouping rules.

7. WHERE filters source rows before grouping. HAVING filters groups after grouping.

8. Rows with NULL in the grouping column form a NULL group.

9. No. Use ORDER BY when output order matters.

10. The join may duplicate source rows, causing an aggregate such as SUM to add the same underlying value more than once.

Scenario:

An employees table contains:
- employee_id
- department
- salary

Task 1:
Write a query that returns the department and number of employees in each department.

Task 2:
Extend the query to calculate total salary and average salary per department.

Task 3:
Sort the departments by employee count from highest to lowest.

Task 4:
Return only departments containing at least five employees.

Try solving each task before viewing the solution.

Task 1: Count employees by department.

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

Task 2: Add salary summaries.

SELECT
department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department;

Task 3: Sort by employee count.

SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department ASC;

Task 4: Keep departments with at least five employees.

SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY employee_count DESC, department ASC;

Explanation:
GROUP BY creates department groups, aggregate functions calculate each group's summary, ORDER BY sorts the results, and HAVING filters groups according to their employee count.

Choose one answer for each question. The correct answers and explanations are included below.

Q1. What is GROUP BY in SQL?
Answer: GROUP BY combines rows with matching values into groups so aggregate functions can calculate summaries for each group.

Q2. Why is GROUP BY useful?
Answer: It supports reports such as total sales by category, employee count by department, and average salary by team.

Q3. Can GROUP BY be used without aggregate functions?
Answer: Yes. It can return distinct grouping combinations, although SELECT DISTINCT is often clearer when the goal is simply to remove duplicate result combinations.

Q4. Which aggregate functions are commonly used with GROUP BY?
Answer: COUNT, SUM, AVG, MIN, and MAX.

Q5. What happens when you group by multiple columns?
Answer: Each distinct combination of grouping values becomes a separate group.

Q6. Why can an ungrouped column in SELECT cause an error?
Answer: The column may contain multiple values within a group, so SQL cannot determine a single valid value for that group.

Q7. What is the difference between WHERE and HAVING?
Answer: WHERE filters source rows before grouping. HAVING filters groups after grouping and aggregate calculations.

Q8. Can WHERE and HAVING appear in the same query?
Answer: Yes. WHERE can reduce the input rows, and HAVING can filter the resulting groups.

Q9. What does COUNT(*) count?
Answer: Every row in the group, including rows containing NULL in individual columns.

Q10. What is the difference between COUNT(*) and COUNT(column)?
Answer: COUNT(*) counts all rows. COUNT(column) counts only rows where that column is not NULL.

Q11. Does SUM include NULL values as zero?
Answer: No. SUM ignores NULL inputs. If every input is NULL, SUM returns NULL.

Q12. How does AVG handle NULL values?
Answer: AVG ignores NULL values when calculating the average.

Q13. How does GROUP BY handle NULL values?
Answer: Rows with NULL in the same grouping column are placed in the same group.

Q14. Does GROUP BY sort the output?
Answer: No. Use ORDER BY to request a specific order.

Q15. Can you group by an expression?
Answer: Yes. For example, DATE_TRUNC('month', created_at) can group timestamps by month.

Q16. Can GROUP BY be combined with JOIN?
Answer: Yes. Joining related tables and grouping the results is a common reporting pattern.

Q17. Why might COUNT(*) be misleading after a LEFT JOIN?
Answer: A preserved row can remain even when no matching row exists on the right. COUNT(*) counts that row, while COUNT(right_table.primary_key) can return zero for the unmatched relationship.

Q18. Why can SUM return an incorrect total after a join?
Answer: If the join multiplies rows, SUM may add the same underlying values more than once. Check the relationship cardinality and aggregation level.

Q19. Is SUM(DISTINCT amount) a reliable fix for duplicate joins?
Answer: Not generally. Different legitimate records can share the same amount, so DISTINCT can remove valid contributions.

Q20. What is the difference between GROUP BY and DISTINCT?
Answer: GROUP BY forms groups and supports aggregate calculations. DISTINCT removes duplicate output rows or combinations.

Q21. Can a GROUP BY query return one row?
Answer: Yes. If all input rows belong to one group, or if the grouping values produce only one distinct combination, it can return one grouped row.

Q22. What happens when a grouped query has no input rows?
Answer: With GROUP BY, no groups are produced, so no result rows are returned. An aggregate query without GROUP BY can behave differently and may return one aggregate row.

Q23. How do you filter groups with more than five employees?
Answer: GROUP BY department and use HAVING COUNT(*) > 5.

Q24. How can GROUP BY performance be investigated?
Answer: Inspect EXPLAIN or EXPLAIN ANALYZE when safe, and examine the plan, input row count, joins, filters, and aggregation strategy.

Q25. What should you decide before writing a GROUP BY query?
Answer: Decide what one result row should represent, then choose the grouping columns and aggregate functions accordingly.

In this lesson, you learned:

- GROUP BY creates groups of rows sharing the same grouping values.
- COUNT, SUM, AVG, MIN, and MAX calculate summaries for each group.
- Grouping by multiple columns uses each distinct combination.
- Selected expressions must be grouped, aggregated, or otherwise valid under PostgreSQL's grouping rules.
- NULL values in a grouping column form a group.
- WHERE filters rows before grouping.
- HAVING filters groups after aggregation.
- GROUP BY does not guarantee sorted output; use ORDER BY.
- Joins can multiply rows and inflate aggregate results if their relationships are not understood.
- Query plans can help diagnose aggregation performance.

The most important habit is to define what one result row represents before writing the query. If each row should represent one department, group by department. If each row should represent one category and status combination, group by both.

Avoid these frequent errors when writing grouped SQL queries.