SQL Functions: Aggregate and Scalar Functions

Learn how SQL functions calculate, transform, and summarize data with practical examples of aggregate, scalar, and dialect-specific functions.

SQL functions are built-in operations that accept zero or more inputs and return a computed value. They can calculate numbers, transform text, summarize rows, work with dates, or prepare values for display.

Functions are commonly used in SELECT expressions, but they can also appear in clauses such as WHERE, ORDER BY, GROUP BY, and HAVING. A function call uses a function name followed by parentheses containing one or more arguments:

FUNCTION_NAME(argument1, argument2)

For example, ROUND(price, 2) rounds a numeric value to two decimal places. A function with no arguments can still require parentheses, as in CURRENT_TIMESTAMP or a database-specific current-time function.

Aggregate and scalar functions

The two main categories are aggregate functions and scalar functions.

Aggregate function: summarizes values from multiple rows into one value for the complete result or for each group.
Scalar function: evaluates an individual input value or row and returns one value for each applicable row.

For example, AVG(price) can return one average for a complete table or one average per category. By contrast, UPPER(product_name) produces a transformed value for each product row.

Aggregate versus scalar functions

Input rows: aggregate functions process a collection of rows; scalar functions process one value or row at a time.
Output: an aggregate usually returns one value for the query or one value per group; a scalar normally returns one value per input row.
Common clauses: aggregates are especially common in SELECT, GROUP BY, and HAVING; scalar functions commonly appear in SELECT, WHERE, and ORDER BY.
Examples: SUM(amount) is aggregate, while LOWER(email) and ROUND(price, 2) are scalar.

Aggregate functions

An aggregate function summarizes a set of rows. Without GROUP BY, the database generally treats the qualifying rows as one set and returns one summary row.

Common aggregate functions

AVG(): calculates the arithmetic mean of numeric values.
COUNT(): counts rows or non-NULL values.
SUM(): adds numeric values.
MIN(): returns the smallest value according to the data type's ordering.
MAX(): returns the greatest value according to the data type's ordering.

For focused lessons, see SQL AVG, SQL COUNT, SQL SUM, SQL MIN, and SQL MAX.

Aggregating an entire result

This query calculates several summaries for the orders table:

SELECT
    COUNT(*) AS order_rows,
    COUNT(amount) AS rows_with_amount,
    AVG(amount) AS average_amount,
    SUM(amount) AS total_amount,
    MIN(amount) AS lowest_amount,
    MAX(amount) AS highest_amount
FROM orders;

COUNT(*) counts every row selected from orders. COUNT(amount) counts only rows where amount is not NULL. The aliases make the result columns easier to understand; review SQL aliases for more examples.

COUNT(*) and COUNT(column)

NULL means a missing or unknown value. It is not the same as zero and not the same as an empty string.

COUNT(*) counts rows, including rows whose individual columns contain NULL.
COUNT(column_name) counts only non-NULL values in that column.

Consequently, these two expressions can produce different results:

SELECT COUNT(*) AS all_rows,
       COUNT(phone_number) AS rows_with_phone
FROM customers;

NULL values and aggregate calculations

Most SQL systems ignore NULL inputs when calculating AVG(), SUM(), MIN(), and MAX(). This means an average is normally calculated from known numeric values, not from an assumed zero for each missing value.

If every input to an aggregate is NULL, the result of AVG(), SUM(), MIN(), or MAX() is commonly NULL. Exact behavior and special cases can vary by database system.

Grouped aggregate results

GROUP BY divides result rows into groups before aggregate calculations are performed. This query returns one summary row for each product category:

SELECT
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price,
    MAX(price) AS highest_price
FROM products
GROUP BY category_id;

Every selected column that is not inside an aggregate generally must appear in the GROUP BY clause. For example, category_id identifies each output group.

WHERE versus HAVING

WHERE filters individual rows before aggregation. HAVING filters groups after aggregate values have been calculated.

SELECT
    category_id,
    SUM(amount) AS category_sales
FROM sales
WHERE sale_status = 'completed'
GROUP BY category_id
HAVING SUM(amount) > 1000;

Here, WHERE removes rows that are not completed sales. The remaining rows are grouped, and HAVING keeps only categories whose total exceeds 1000. See SQL WHERE and SQL HAVING for related concepts.

FIRST() and LAST()

Some database systems provide functions named FIRST() and LAST(). They may return a value from a set, but they are not portable standard ways to select the earliest or latest row.

A relational table has no inherent order. Without an explicit ORDER BY, there is no guaranteed first or last row. When row order matters, sort by the intended columns and use the database's row-limiting or ranking syntax:

SELECT order_id, customer_id, order_date
FROM orders
ORDER BY order_date ASC, order_id ASC
FETCH FIRST 1 ROW ONLY;

Some systems use LIMIT 1 or TOP 1 instead. See SQL ORDER BY and SQL row limiting.

Scalar functions

A scalar function transforms or calculates a value for each applicable row. Scalar functions are useful for creating derived columns, which are output columns calculated from source values rather than stored directly in the table.

Text conversion

Use uppercase and lowercase functions to standardize text in query results:

SELECT
    UPPER(customer_name) AS name_uppercase,
    LOWER(email) AS email_lowercase
FROM customers;

Common names include UPPER() and UCASE() for uppercase conversion, and LOWER() and LCASE() for lowercase conversion. The supported name depends on the SQL dialect.

Extracting part of a string

Substring functions extract part of a text value. A common form accepts the source text, a starting position, and a length:

SELECT
    product_code,
    SUBSTRING(product_code, 1, 3) AS code_prefix
FROM products;

Depending on the database, the equivalent function may be MID(), SUBSTRING(), or SUBSTR(). Starting positions are often one-based, but verify the target system's rules.

Text length

Text-length functions return the length of a string. Common names are LEN(), LENGTH(), and CHAR_LENGTH():

SELECT
    customer_name,
    CHAR_LENGTH(customer_name) AS name_length
FROM customers;

Length behavior can differ for trailing spaces, multibyte characters, and character versus byte counts. Test representative data when moving a query between systems.

Rounding numbers

ROUND() controls the number of decimal places shown or returned in a numeric calculation:

SELECT
    product_name,
    ROUND(price * 1.075, 2) AS price_with_tax
FROM products;

The first argument is the number to round, and the second commonly specifies the number of decimal places. Rounding rules and return types can vary by dialect.

Current date and time

Database systems provide functions or expressions for the current date and time. Common alternatives include NOW() and the more portable-looking standard expression CURRENT_TIMESTAMP:

SELECT
    product_name,
    CURRENT_TIMESTAMP AS query_time
FROM products;

The value may be evaluated once for the statement or separately according to the database implementation. Time zone behavior should also be checked in the product documentation.

Formatting output

FORMAT() or an equivalent function can prepare numbers and dates for display. Formatting is presentation-oriented and may return text:

SELECT FORMAT(price, 2) AS display_price
FROM products;

Do calculations before formatting. A formatted value such as '1,234.50' may be text and therefore unsuitable for later arithmetic, sorting as a number, or numeric comparisons.

Function portability and SQL dialects

A SQL dialect is a database product's particular SQL syntax and function set. MySQL, SQL Server, PostgreSQL, Oracle, SQLite, and other systems can differ in function names, argument order, return types, supported features, string indexing, and date-time behavior.

Uppercase: use UPPER() or the product's equivalent such as UCASE().
Lowercase: use LOWER() or an equivalent such as LCASE().
Substring: check whether the system uses SUBSTRING(), SUBSTR(), or MID(), and verify argument order.
String length: check LEN(), LENGTH(), or CHAR_LENGTH(), including whether the result counts characters or bytes.
Current time: check NOW(), CURRENT_TIMESTAMP, or the system's date-time expression.
First or last row: do not rely on FIRST() or LAST(); use ORDER BY with limiting or ranking syntax.

When portability matters, consult the target database documentation and test both the query syntax and the returned data types.

Reusable SQL function patterns

These patterns show the general structure of common function queries:

-- One aggregate result for the selected rows
SELECT AGGREGATE_FUNCTION(column_name) AS result_name
FROM table_name;

-- One aggregate result per group
SELECT grouping_column,
       AGGREGATE_FUNCTION(value_column) AS result_name
FROM table_name
GROUP BY grouping_column;

-- A scalar transformation for each row
SELECT SCALAR_FUNCTION(column_name) AS transformed_value
FROM table_name;

-- Filter groups using an aggregate result
SELECT grouping_column,
       SUM(value_column) AS total_value
FROM table_name
GROUP BY grouping_column
HAVING SUM(value_column) > threshold;

Troubleshooting SQL functions

COUNT(column_name) returns fewer rows than expected: the column contains NULL values. Use COUNT(*) when every row should be counted.
An aggregate query fails when a regular column is selected: the non-aggregated column may be missing from GROUP BY. Add it, aggregate it, or remove it from SELECT.
A function is not recognized: the database may use another dialect. Try an equivalent such as UPPER(), SUBSTRING(), or LENGTH(), and check its argument order.
FIRST() or LAST() returns an unexpected record: no reliable row order was specified. Add an ORDER BY using the intended date or identifier, then limit or rank the result.
Formatted output cannot be used in arithmetic: the formatting function returned text. Calculate first and format only in the final output.
String lengths or substring results change between databases: indexing rules, character handling, and function signatures differ. Check the target dialect with representative data.

Exam note: Remember the processing distinction: WHERE filters rows before grouping, while HAVING filters groups after aggregation. Also remember that COUNT(*) counts rows, whereas COUNT(column) ignores NULL values.