Which One Is Not an Aggregate Function? SQL Guide

A scalar function such as UPPER() is not an aggregate function: it transforms a value in an individual row, while an aggregate function calculates a result from a set of rows. If this question comes from a multiple-choice quiz, the exact answer depends on its options; look for the choice that works on individual values rather than summarizing a group.

What an Aggregate Function Does

In SQL, an aggregate function takes values from multiple rows and produces a summary value. Depending on the query, the function can summarize the whole set of rows selected by the query or summarize each group formed by GROUP BY. Common reasons to use aggregates include counting records, totaling sales, calculating an average, and finding the smallest or largest value.

For example, a sales table might contain one row per transaction. SUM(amount) can calculate total sales across the table, while SUM(amount) combined with GROUP BY region can calculate a separate total for each region. The function is the same; the grouping in the query changes which rows contribute to each result.

An aggregate typically reduces many input values to one output value per group. That reduction is the key idea behind the term. The output might be a count, a number, a date, or another value, but it represents a collection of rows rather than simply modifying one row at a time.

Common SQL Aggregate Functions

These are widely used aggregate functions in SQL. Exact support and some details can vary by database system, but the following functions are familiar across common relational databases:

COUNT() counts rows or non-NULL values, depending on the form used.
SUM() adds the non-NULL values in a numeric expression.
AVG() calculates the average of the non-NULL values in a numeric expression.
MIN() returns the lowest value in the set, according to the data type’s ordering.
MAX() returns the highest value in the set, according to the data type’s ordering.

Some database systems also provide aggregates such as standard deviation, variance, string concatenation, or statistical summaries. Their names and syntax are not identical across products. When working with a particular database, check its documentation rather than assuming that a function available in one system is available in another.

It is easy to overlook COUNT() because it returns a single number, but counting is a summary operation and therefore an aggregate. The same is true when the count is grouped: the query returns a count for each group instead of one count for the full set.

Why UPPER and Other Scalar Functions Are Different

A scalar function returns a value for each input row or expression. It does not, by itself, combine values from multiple rows into a group summary. For example, UPPER(customer_name) converts a name to uppercase for a row, and ROUND(price, 2) rounds a price to two decimal places. If a query has 50 matching rows, either expression can produce 50 corresponding results.

Other common scalar functions include text transformations, date-part extraction, arithmetic operations, and conditional expressions. The particular function catalog depends on the SQL product. Examples include LOWER(), ABS(), and date or string functions. A scalar function may be used in a query that also contains aggregates, but that does not make the scalar function itself an aggregate.

For instance, a query could use ROUND(AVG(price), 2). Here, AVG(price) is the aggregate: it calculates an average across rows. ROUND() is scalar: it rounds the single average value produced by the aggregate. Function nesting does not erase the difference between the two roles.

How GROUP BY Changes the Result

Suppose a table named orders has columns called region and amount. This query calculates one total for each region:

SELECT region, SUM(amount) AS total_amount FROM orders GROUP BY region;

GROUP BY region divides the selected rows into groups with matching region values. The aggregate SUM(amount) then calculates a total within each group. The result has one row per region represented in the grouped data, rather than one row per order.

Without GROUP BY, an aggregate query such as SELECT SUM(amount) FROM orders; normally produces one summary for all qualifying rows. A query that selects an ordinary column alongside an aggregate usually needs to group by that column. Otherwise, the database cannot determine which single region value should accompany the total. Database rules differ in some advanced cases, but grouping the nonaggregated column is the portable approach.

This distinction also explains a common query error: selecting region and SUM(amount) without grouping by region is generally invalid. The issue is not that region is a nonaggregate function; it is a column value that has not been reduced to one value per result row.

Common Multiple-Choice Traps

When a question asks which item is not an aggregate function, first identify what each option does. If one option transforms, tests, or calculates something for each row, while the others summarize groups, that row-level function is the likely answer. A common quiz set might include SUM(), AVG(), COUNT(), and UPPER(); in that set, UPPER() is not an aggregate.

Do not confuse a function with a clause. GROUP BY, WHERE, and HAVING are SQL clauses, not aggregate functions.
Do not assume every function returning one value is an aggregate. ABS(-8) returns one value, but it operates on one expression, not a set of rows.
Do not mistake a column name for an aggregate. A selected column such as region is neither an aggregate function nor a scalar function.
Check the inputs and purpose. A function’s name alone may not make its category obvious, especially in product-specific SQL.

Another potential source of confusion is that scalar functions can be applied to aggregate results, and expressions can combine both types. In ROUND(AVG(amount), 2), the outer function does not summarize rows. It receives the aggregate’s result and formats or rounds that result.

NULL Values, COUNT, and DISTINCT

NULL handling matters when comparing aggregate examples. In most SQL systems, SUM(), AVG(), MIN(), and MAX() ignore NULL inputs. They operate on the available non-NULL values; if there are no applicable values, the result is often NULL. Consult the database documentation for product-specific details.

COUNT(*) counts rows, including rows whose columns contain NULL. By contrast, COUNT(amount) counts rows where amount is not NULL. Thus, if a table has five rows and two have a NULL amount, COUNT(*) returns five while COUNT(amount) returns three. Both forms are aggregates, but they answer different questions.

The optional DISTINCT keyword can change which values contribute. For example, COUNT(DISTINCT customer_id) counts distinct non-NULL customer IDs in the relevant set or group. DISTINCT is not itself an aggregate function; it modifies how an aggregate handles its input. Similarly, a query-level SELECT DISTINCT removes duplicate result rows and is not the same operation as an aggregate.

Aggregates in HAVING and Window Queries

SQL uses WHERE to filter individual rows before grouping. Since ordinary aggregates summarize groups, a query generally uses HAVING to filter groups based on an aggregate result. For example, HAVING SUM(amount) > 1000 keeps only groups whose total exceeds 1,000. The number is simply an example; its meaning depends on the data and currency or units.

A related feature is the windowed aggregate, such as SUM(amount) OVER (PARTITION BY region). It calculates a summary for a window of rows but can retain the individual rows in the output. This differs from a grouped aggregate query, which normally returns one row per group. The function SUM() is still an aggregate; the OVER clause changes how its result is associated with rows. Database support and precise syntax vary.

How to Identify the Right Answer

For a quick test, ask what happens when the input table contains many rows. Does the function combine their values into a summary for the full set or for each group? That points to an aggregate. Does it instead return a corresponding transformed or calculated value for each row? That points to a scalar function.

List the choices and mark familiar aggregate functions such as COUNT(), SUM(), AVG(), MIN(), or MAX().
For the remaining choices, ask whether each one summarizes a set of rows or acts on an individual expression.
If the question gives no options, avoid claiming there is one uniquely identifiable answer. Many scalar functions are not aggregates; UPPER() is one clear example.
If a function is unfamiliar, check the documentation for the database named in the question. SQL products may add their own functions and may use different names.

This approach is more reliable than memorizing a short list. The same function can appear inside a larger expression, and a query can use aggregates and scalar functions together. Classify each function by what it does, rather than by the overall appearance of the query.

Frequently Asked Questions

Is COUNT an aggregate function?
Yes. COUNT() summarizes rows or non-NULL expression values. COUNT(*) counts rows, while COUNT(column_name) excludes rows where that column is NULL.

Is GROUP BY an aggregate function?
No. GROUP BY is a SQL clause that forms groups of rows. Aggregate functions such as SUM() or COUNT() calculate a summary for each group.

Is ROUND an aggregate function?
Usually, ROUND() is a scalar function. It rounds an individual numeric expression, or it can round the result returned by an aggregate such as AVG().

Can a query use scalar and aggregate functions together?
Yes. For example, a query can round an average with ROUND(AVG(price), 2). The average aggregates values across rows; rounding acts on the resulting value.

What is the answer if no choices are provided?
There is no single unique answer without options because many functions are not aggregates. A clear example is UPPER(), which transforms text one value at a time rather than summarizing a set of rows.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.