Combining multiple aggregates in SQL - Step-by-Step Execution

Concept Flow - Combining multiple aggregates

Start Query

↓

Scan Table Rows

↓

Apply Aggregate Functions

↓

SUM()

↓

Combine Results

↓

Return Single Row with All Aggregates

The query scans the table, applies each aggregate function to all rows, then combines these results into one output row.

Execution Sample

SQL

SELECT SUM(sales), COUNT(sales), AVG(sales) FROM orders;

This query calculates the total sales, number of sales, and average sale amount from the orders table.

Execution Table

Step	Action	SUM(sales)	COUNT(sales)	AVG(sales)
1	Start scanning orders table	NULL	NULL	NULL
2	Read row 1: sales=100	100	1	100
3	Read row 2: sales=200	300	2	150
4	Read row 3: sales=50	350	3	116.67
5	Read row 4: sales=150	500	4	125
6	All rows processed	500	4	125
7	Return combined aggregates	500	4	125

💡 All rows processed, aggregates computed and combined into one result row.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
SUM(sales)	0	100	300	350	500	500
COUNT(sales)	0	1	2	3	4	4
AVG(sales)	NULL	100	150	116.67	125	125

Key Moments - 2 Insights

Why does AVG(sales) change after each row instead of only at the end?

AVG is calculated as SUM divided by COUNT at each step, so it updates as new rows are processed (see execution_table rows 2-5).

Why do all aggregates return a single row instead of multiple rows?

Aggregate functions summarize all rows into one result, so the query returns one combined row with all aggregates (see execution_table row 7).

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what is the COUNT(sales)?

A 2

B 4

C 3

D 1

💡 Hint

Check the COUNT(sales) column in execution_table row with Step 4.

At which step does SUM(sales) reach 500?

A Step 3

B Step 5

C Step 6

D Step 2

💡 Hint

Look at the SUM(sales) column and find when it first equals 500.

If a new row with sales=100 is added, what will COUNT(sales) be at final?

A 5

B 4

C 6

D 3

💡 Hint

COUNT increases by 1 for each new row processed (see variable_tracker COUNT row).

Concept Snapshot

Combining multiple aggregates:
Use multiple aggregate functions (SUM, COUNT, AVG) in one SELECT.
Each processes all rows and returns one combined result row.
AVG is SUM divided by COUNT, updated as rows are read.
Result is a single row with all aggregates together.

Full Transcript

This visual execution shows how SQL combines multiple aggregate functions in one query. The query scans each row of the orders table, updating SUM, COUNT, and AVG step by step. SUM adds sales amounts, COUNT counts rows, and AVG calculates the average as SUM divided by COUNT. After all rows are processed, the query returns one row with all three aggregate results combined. This helps beginners see how aggregates work together and produce a single summary output.

Practice

1. What does combining multiple aggregate functions in a single SQL query allow you to do?

easy

A. Get several summary values like totals and averages in one result

B. Run multiple queries at the same time

C. Create new tables automatically

D. Sort data without using ORDER BY

Solution

1. Step 1: Understand aggregate functions

   Aggregate functions like SUM() and AVG() calculate summary values from data.

2. Step 2: Combining aggregates in one query

   Using commas, you can list multiple aggregates in SELECT to get many summaries at once.

3. Final Answer:

   Get several summary values like totals and averages in one result -> Option A

4. Quick Check:

   Multiple aggregates = multiple summaries [OK]

Hint: Use commas to separate aggregates in SELECT [OK]

Common Mistakes:

• Thinking multiple queries run simultaneously
• Confusing aggregates with table creation
• Assuming aggregates sort data automatically

2. Which of the following is the correct syntax to combine multiple aggregates in one SQL SELECT statement?

easy

A. SELECT SUM(price) AND AVG(price) FROM sales;

B. SELECT SUM(price), AVG(price) FROM sales;

C. SELECT SUM(price) AVG(price) FROM sales;

D. SELECT SUM(price) OR AVG(price) FROM sales;

Solution

1. Step 1: Check aggregate separation

   Aggregates must be separated by commas in SELECT clause.

2. Step 2: Identify correct syntax

   SELECT SUM(price), AVG(price) FROM sales; uses commas correctly; others use AND, OR, or no separator which is invalid.

3. Final Answer:

   SELECT SUM(price), AVG(price) FROM sales; -> Option B

4. Quick Check:

   Aggregates separated by commas [OK]

Hint: Separate aggregates with commas, not AND/OR [OK]

Common Mistakes:

• Using AND or OR instead of commas
• Omitting commas between aggregates
• Writing aggregates without any separator

3. Given the table orders with column amount, what will this query return?

SELECT COUNT(*), MAX(amount), MIN(amount) FROM orders;

medium

A. Number of rows, highest amount, lowest amount

B. Sum of amounts, average amount, number of rows

C. Only the highest amount

D. Syntax error due to multiple aggregates

Solution

1. Step 1: Understand each aggregate

   COUNT(*) counts rows, MAX(amount) finds highest value, MIN(amount) finds lowest value.

2. Step 2: Combine results

   All three aggregates return one row with three summary values: count, max, and min.

3. Final Answer:

   Number of rows, highest amount, lowest amount -> Option A

4. Quick Check:

   COUNT, MAX, MIN = count, max, min [OK]

Hint: Each aggregate returns one summary value in the result [OK]

Common Mistakes:

• Confusing COUNT(*) with SUM(amount)
• Expecting multiple rows instead of one
• Thinking multiple aggregates cause syntax error

4. Identify the error in this SQL query:

SELECT SUM(price), AVG(price) FROM sales GROUP BY category;

medium

A. No error, query is correct

B. Cannot use SUM and AVG together

C. Missing GROUP BY column in SELECT clause

D. GROUP BY should be after WHERE clause

Solution

1. Step 1: Check GROUP BY usage

   When using GROUP BY category, category must appear in SELECT to show groups.

2. Step 2: Identify missing column

   Query selects only aggregates but misses category column, causing error or unexpected output.

3. Final Answer:

   Missing GROUP BY column in SELECT clause -> Option C

4. Quick Check:

   GROUP BY columns must appear in SELECT [OK]

Hint: Include GROUP BY columns in SELECT list [OK]

Common Mistakes:

• Omitting GROUP BY column in SELECT
• Thinking SUM and AVG can't be combined
• Misplacing GROUP BY clause

5. You want to find the total sales, average sales, and number of sales for each product category in a sales table with columns category and amount. Which query correctly combines these aggregates?

hard

A. SELECT category, SUM(amount) AND AVG(amount) AND COUNT(*) FROM sales GROUP BY category;

B. SELECT SUM(amount), AVG(amount), COUNT(*) FROM sales;

C. SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales;

D. SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales GROUP BY category;

Solution

1. Step 1: Include category in SELECT and GROUP BY

   To get aggregates per category, category must be in SELECT and GROUP BY.

2. Step 2: Combine aggregates with commas

   SUM(amount), AVG(amount), COUNT(*) are combined with commas to get totals, averages, and counts.

3. Final Answer:

   SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales GROUP BY category; -> Option D

4. Quick Check:

   GROUP BY category with aggregates = SELECT category, SUM(amount), AVG(amount), COUNT(*) FROM sales GROUP BY category; [OK]

Hint: Group by category and list aggregates with commas [OK]

Common Mistakes:

• Missing GROUP BY clause
• Using AND instead of commas between aggregates
• Not including category in SELECT when grouping