Combining multiple aggregates in SQL - Time & Space Complexity

Time Complexity: Combining multiple aggregates

O(n)

Understanding Time Complexity

When we combine several aggregate functions in one query, it is important to know how the work grows as the data grows.

We want to understand how the time to get results changes when the number of rows increases.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT
  COUNT(*) AS total_orders,
  SUM(amount) AS total_amount,
  AVG(amount) AS average_amount
FROM orders
WHERE order_date >= '2024-01-01';

This query calculates the total number of orders, the sum of amounts, and the average amount for orders from 2024 onward.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning each row in the filtered orders table once.
• How many times: Once per row that meets the date condition.

How Execution Grows With Input

As the number of orders grows, the database must look at each matching row once to update all aggregates.

Input Size (n)	Approx. Operations
10	About 10 row checks and updates
100	About 100 row checks and updates
1000	About 1000 row checks and updates

Pattern observation: The work grows directly with the number of rows; doubling rows doubles work.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line with the number of rows processed.

Common Mistake

[X] Wrong: "Adding more aggregate functions makes the query take longer by multiplying the work."

[OK] Correct: All aggregates are calculated in one pass over the data, so adding more aggregates only adds a small fixed amount of work per row, not a full extra pass.

Interview Connect

Understanding how combining aggregates affects performance helps you write efficient queries and explain your reasoning clearly in interviews.

Self-Check

"What if we added a GROUP BY clause to this query? How would the time complexity change?"