SQL Aggregate Functions — What does this query return?

Why This Question Matters

At first glance, this query looks like a "gimme"—something any junior dev could answer in five seconds. But that's exactly why it's a great teaching tool. It touches on the fundamental way SQL filters data and aggregates results.

The confusion usually doesn't stem from the COUNT(*) itself, but from how developers conceptualize the order of operations. Newcomers often struggle with whether the filter happens before or after the count, or they get tripped up by how NULL values or specific conditions interact with aggregate functions. Understanding this is the difference between writing a query that works and writing one that actually returns the data you think it is.

Understanding the Code

Let's look at the snippet again:

SELECT COUNT(*) FROM orders
WHERE status = 'completed'
  AND amount > 100

Here is what's actually happening under the hood. SQL doesn't read from top to bottom; it follows a specific logical processing order.

First, the database goes to the FROM clause to identify the table (orders). Then, it hits the WHERE clause. This is the "filtering" phase. The engine scans the rows and throws out anything that doesn't meet both criteria: the status must be exactly 'completed' and the amount must be strictly greater than 100.

Only after the dataset has been pruned down to these specific rows does the SELECT clause kick in. The COUNT(*) then simply counts how many rows survived the filter.

It’s a linear pipeline: Table $\rightarrow$ Filter $\rightarrow$ Count.

Finding the Correct Answer

The correct answer is Option B, which states that the query returns the total number of completed orders with an amount greater than 100.

Why not the other options?

Common Mistakes Developers Make

Even experienced devs trip up on a few things when writing these types of queries.

1. The NULL Trap
If you change amount > 100 to amount != 100, you might expect to get every row that isn't 100. But in SQL, NULL is not a value—it's the absence of a value. NULL > 100 is not true, and NULL != 100 is also not true. It's "Unknown." Those rows vanish from your count.

2. Misunderstanding COUNT(*) vs COUNT(column)
I see this all the time. COUNT(*) counts every row that meets the criteria, including those with NULL values in some columns. COUNT(column_name) only counts rows where that specific column is not NULL. In this specific query, it doesn't matter, but in a more complex join, it can lead to wildly different numbers.

3. Over-reliance on DISTINCT
Sometimes developers throw DISTINCT inside the count—COUNT(DISTINCT order_id)—when they don't need to. If your orders table has a primary key, COUNT(*) is faster and more idiomatic.

Real-World Usage

In a production environment, you'll rarely see a query this simple in isolation. Usually, this logic is part of a dashboard or a reporting tool.

For example, if you're building an admin panel for an e-commerce site, you might use this to calculate "High-Value Completed Sales." You'd likely wrap this in a more complex query or use it as a subquery to calculate a percentage:

SELECT 
  (SELECT COUNT(*) FROM orders WHERE status = 'completed' AND amount > 100) * 100.0 / 
  (SELECT COUNT(*) FROM orders) as high_value_percentage;

From a performance perspective, if your orders table has millions of rows, this query will be slow unless you have a composite index on (status, amount). Without an index, the database has to perform a full table scan, which is a great way to kill your app's responsiveness.

Key Takeaways