Aggregate and Non-Aggregate Functions in SQL Explained

Aggregate functions in SQL calculate a result from multiple rows, such as a sum or count; non-aggregate functions return a value for each individual row. Knowing which kind you are using helps you write correct queries, especially when combining calculations with GROUP BY, WHERE, and HAVING.

What aggregate and non-aggregate mean

The difference is about the level at which a function operates. An aggregate function takes values from a set of rows and produces one result for that set. The set might be the whole filtered table or one group of rows. A non-aggregate function, often called a scalar function, evaluates an input value or expression for each row and returns a row-level result.

For example, SUM(amount) can calculate one total from many order amounts, while UPPER(customer_name) converts each customer name to uppercase separately. Aggregates are useful for reporting and summaries; scalar functions are useful for transforming, comparing, or calculating values within records. A query can use both kinds, but SQL rules determine how their results can appear together.

How aggregate functions work

Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX. A query without GROUP BY applies an aggregate to the rows that remain after filtering and returns a summary row. For instance, assuming an Orders table has an amount column:

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount
FROM Orders
WHERE status = 'paid';

This returns the number of paid order rows, their total amount, and their average amount. The aggregate does not return each order separately. Add GROUP BY to calculate a separate result for each category, customer, date, or other grouping value.

Aggregates summarize rows, but they do not necessarily mean that the database reads the table only once or uses a particular execution strategy. The query optimizer chooses a plan based on the database engine, indexes, data, and query. The important point for a SQL reader is the result: an aggregate condenses input rows into summary values.

What non-aggregate functions do

Non-aggregate functions generally produce one result for each row on which the expression is evaluated. Examples include string functions such as LOWER and UPPER, numeric functions such as ABS and ROUND, and conditional expressions such as CASE. The exact functions and date-handling syntax vary among database systems.

For example, this query still returns one output row for each matching order:

SELECT order_id,
       UPPER(status) AS status_upper,
       ROUND(amount, 2) AS amount_rounded
FROM Orders
WHERE status IS NOT NULL;

UPPER and ROUND transform values on a row-by-row basis. They do not combine different orders into a single result. A scalar expression can also use multiple columns from the same row. For example, a CASE expression can label an order as large or small based on its amount.

Some functions are built into one database but absent or differently named in another. Date functions, string concatenation, and conditional functions are common sources of dialect differences. Check the documentation for the database you use rather than assuming that a function available in PostgreSQL, SQL Server, MySQL, or Oracle has identical syntax everywhere.

Using GROUP BY to summarize categories

GROUP BY divides the filtered rows into groups that share the same value or combination of values. Aggregate functions then calculate a result within each group. For example, to count orders and total their amounts by customer:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM Orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY total_amount DESC;

The result has one row per customer represented among the paid orders. Each customer’s count and total are calculated from that customer’s qualifying rows. With multiple grouping columns, such as customer_id and order_date, the result has one row per distinct combination of those values.

A key rule is that a selected expression must make sense for each output group. In many databases, a selected column that is not inside an aggregate must be listed in GROUP BY. Some systems allow particular exceptions when they can establish a functional dependency, but relying on that behavior can reduce portability. If a query reports that a column must appear in the GROUP BY clause or be used in an aggregate, check whether you intended to group by it or summarize it.

WHERE and HAVING filter at different stages

WHERE filters individual input rows before grouping. HAVING filters groups after the database has formed them and calculated aggregate results. For example, this query first keeps paid orders, groups them by customer, then returns only customers whose paid-order total exceeds 1,000:

SELECT customer_id, SUM(amount) AS total_amount
FROM Orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Use WHERE for a condition on a row, such as status or order date. Use HAVING for a condition on a group, such as a sum, count, or average. Filtering rows early can also reduce the amount of data that must be grouped, though the optimizer may transform a query while preserving its meaning.

One common mistake is trying to put an aggregate condition such as SUM(amount) > 1000 in WHERE. Since WHERE runs before the group total exists, that is not valid in ordinary SQL. Put the condition in HAVING, or use a subquery if the query needs a different structure.

NULL, COUNT, and DISTINCT behavior

Aggregate functions have important rules for NULL. Most aggregates ignore null input values. Thus, AVG(amount) averages the non-null amounts rather than treating missing amounts as zero. SUM, AVG, MIN, and MAX return NULL when there are no non-null values to process. This is different from a zero total or a zero average.

COUNT(*) counts rows, including rows where some or all column values are null.
COUNT(amount) counts rows where amount is not null.
COUNT(DISTINCT customer_id) counts distinct non-null customer IDs.
SUM(DISTINCT amount) adds each distinct non-null amount once; this is not the same as summing every row.

These distinctions matter when checking totals against source data. If the goal is to count orders, COUNT(*) is usually clearer than counting a column that might be null. If a report should display zero instead of null for an empty sum, use a null-handling expression supported by your database, such as COALESCE(SUM(amount), 0). Be sure that zero is appropriate for the meaning of the data; missing information and a genuine zero are not always interchangeable.

How window functions differ from ordinary aggregates

A window function can calculate an aggregate across related rows while preserving individual rows in the result. It uses an OVER clause. For example, SUM(amount) OVER (PARTITION BY customer_id) can show each order alongside the total amount for that order’s customer. Unlike a grouped query that normally returns one row per customer, a windowed query can still return one row per order.

Window functions are related to aggregation but have a different role in query results. Some aggregate functions, including SUM and COUNT, can be used as window functions in SQL systems that support them. Ranking functions such as ROW_NUMBER are window functions, not ordinary aggregate functions. The OVER clause defines the rows considered for each calculation; PARTITION BY creates separate calculation groups without collapsing them into one output row per group.

Common errors and how to avoid them

Most mistakes come from confusing row-level values with group-level values. When a query combines a non-aggregate column and an aggregate, ask what one output row represents. If the answer is one customer, a raw order ID usually cannot identify that customer-level row unless it is grouped or otherwise handled in a valid way.

Unintended grouping: Adding another column to GROUP BY changes the definition of a group and can create more output rows. Group only by the categories the report needs.
Misleading averages: An average of group averages may not equal the average of all underlying records, especially when groups have different sizes. Aggregate the original values or use a weighted calculation when that is the intended measure.
Duplicate counts after joins: A one-to-many join can repeat a row before aggregation. Confirm the join relationships and decide whether the measure should count joined rows or distinct entities.
Nulls mistaken for zeros: Check whether missing values should be excluded, shown as null, or explicitly replaced. Each choice changes how readers interpret the result.

When a summary looks wrong, inspect the rows before aggregation, verify the join keys, and compare a small group with its underlying records. This often reveals whether the issue is the chosen function, the grouping columns, or the rows entering the calculation.

Choosing the right kind of function

Choose based on the output you need, not just on the function’s name. If you need one value per record, use a scalar expression. If you need one summary per dataset or category, use an aggregate, with GROUP BY when the summary should be split into groups. If you need a summary alongside every detail row, consider a window function.

Use non-aggregate expressions for row-level formatting, conversion, classification, or calculations.
Use aggregates for totals, counts, averages, minimums, and maximums across rows.
Use GROUP BY to define which rows contribute to each separate aggregate result.
Use a window function when you need a group-level calculation without collapsing detail rows.

Also consider portability and meaning. SQL dialects differ in their function libraries and in some grouping rules, while null handling and join duplication can affect the numbers regardless of syntax. Clear aliases, explicit grouping, and a quick check of the underlying records make aggregate queries easier to maintain.

Frequently Asked Questions

Is COUNT an aggregate function in SQL?
COUNT is an aggregate function when it summarizes rows, as in COUNT(*) or COUNT(column_name). With an OVER clause, it can instead act as a windowed calculation and preserve detail rows.

Can I use an aggregate function in WHERE?
Normally, no. WHERE filters rows before aggregation, so an aggregate condition belongs in HAVING. A subquery or common table expression can also calculate a summary first and filter its result.

Do non-aggregate functions require GROUP BY?
No. A scalar function used in a query without aggregation does not require GROUP BY. In a grouped query, however, a non-aggregate expression in the select list generally needs to be grouped or otherwise be valid under that database’s grouping rules.

What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts only rows where that column is not null, so the two results can differ when the column has missing values.

Is a window function an aggregate function?
Not necessarily. Some aggregates can be used as window functions by adding OVER, but window functions also include functions such as ROW_NUMBER. Window functions calculate across related rows while generally keeping the individual rows in the output.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.