GROUP BY single and multiple columns in PostgreSQL - Step-by-Step Execution

Concept Flow - GROUP BY single and multiple columns

Start with SELECT query

↓

Identify columns to GROUP BY

↓

Scan table rows

↓

Group rows by unique values of GROUP BY columns

↓

Apply aggregate functions on each group

↓

Return grouped result rows

The query scans the table, groups rows by the specified columns, then calculates aggregates for each group and returns the results.

Execution Sample

PostgreSQL

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

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

First query groups employees by department and counts them. Second query groups by department and role, then calculates average salary.

Execution Table

Step	Action	Input Rows	Group Key	Aggregated Result	Output Rows
1	Scan all rows from employees	10 rows	-	-	-
2	Group rows by department (single column)	10 rows	Sales	Count=3	1 row for Sales
3	Group rows by department (single column)	10 rows	HR	Count=4	1 row for HR
4	Group rows by department (single column)	10 rows	IT	Count=3	1 row for IT
5	Return grouped count results	-	-	-	3 rows total
6	Group rows by department and role (multiple columns)	10 rows	(Sales, Manager)	Avg Salary=70000	1 row for Sales Manager
7	Group rows by department and role (multiple columns)	10 rows	(Sales, Rep)	Avg Salary=50000	1 row for Sales Rep
8	Group rows by department and role (multiple columns)	10 rows	(HR, Recruiter)	Avg Salary=60000	1 row for HR Recruiter
9	Group rows by department and role (multiple columns)	10 rows	(IT, Developer)	Avg Salary=80000	1 row for IT Developer
10	Return grouped average salary results	-	-	-	4 rows total
11	End of query execution	-	-	-	-

💡 All rows processed and grouped by specified columns; aggregates computed and results returned.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 6	After Step 7	After Step 8	After Step 9	Final
Group Keys (single column)	[]	[Sales]	[Sales, HR]	[Sales, HR, IT]	[Sales, HR, IT]	[Sales, HR, IT]	[Sales, HR, IT]	[Sales, HR, IT]	[Sales, HR, IT]
Group Keys (multiple columns)	[]	[]	[]	[]	[(Sales, Manager)]	[(Sales, Manager), (Sales, Rep)]	[(Sales, Manager), (Sales, Rep), (HR, Recruiter)]	[(Sales, Manager), (Sales, Rep), (HR, Recruiter), (IT, Developer)]	[(Sales, Manager), (Sales, Rep), (HR, Recruiter), (IT, Developer)]
Aggregated Counts	{}	{"Sales":3}	{"Sales":3,"HR":4}	{"Sales":3,"HR":4,"IT":3}	{}	{}	{}	{}	{}
Aggregated Avg Salaries	{}	{}	{}	{}	(Sales, Manager): 70000	(Sales, Manager): 70000 (Sales, Rep): 50000	(Sales, Manager): 70000 (Sales, Rep): 50000 (HR, Recruiter): 60000	(Sales, Manager): 70000 (Sales, Rep): 50000 (HR, Recruiter): 60000 (IT, Developer): 80000	(Sales, Manager): 70000 (Sales, Rep): 50000 (HR, Recruiter): 60000 (IT, Developer): 80000

Key Moments - 2 Insights

Why does grouping by multiple columns create more groups than grouping by a single column?

Because grouping by multiple columns considers unique combinations of values from all those columns, not just one. For example, (Sales, Manager) and (Sales, Rep) are different groups even though they share the same department.

What happens if you select a column that is not in GROUP BY or an aggregate function?

The query will cause an error because every selected column must be either grouped or aggregated. This is shown by the grouping keys in the execution_table that must match selected columns.

Visual Quiz - 3 Questions

Look at the execution_table at Step 4, how many groups exist when grouping by department?

A 3 groups

B 4 groups

C 1 group

D 10 groups

💡 Hint

Check the 'Group Key' column at Step 4 showing Sales, HR, IT groups.

At which step does the query start grouping by multiple columns?

A Step 2

B Step 5

C Step 6

D Step 10

💡 Hint

Look for the first step mentioning grouping by department and role in the 'Action' column.

If the employees table had an extra role in Sales, how would the number of groups at Step 9 change?

A It would decrease by 1

B It would increase by 1

C It would stay the same

D It would double

💡 Hint

Adding a new unique (department, role) combination creates a new group as shown in variable_tracker for multiple columns.

Concept Snapshot

GROUP BY syntax:
SELECT columns, aggregate_function(column) FROM table
GROUP BY column1 [, column2, ...];

Groups rows by unique values of specified columns.
Aggregate functions compute summary per group.
Multiple columns create groups by unique combinations.

Full Transcript

This visual execution shows how SQL GROUP BY works with single and multiple columns. The query scans all rows, then groups them by the specified columns. For single column grouping, rows with the same value in that column form one group. For multiple columns, groups form by unique combinations of those columns' values. Aggregates like COUNT or AVG are calculated per group. The execution table traces each step, showing how groups form and aggregates compute. Variable tracking shows how group keys and aggregates build up. Key moments clarify why multiple columns create more groups and why selected columns must be grouped or aggregated. The quiz tests understanding of group counts and grouping steps. The snapshot summarizes syntax and behavior for quick reference.