VMware ESXi and vSphere Cluster Management

SQL Functions: Aggregate and Scalar Functions

Learn how SQL functions calculate, summarize, transform, and format query results with aggregate and scalar function examples.

What is a SQL function?

A SQL function is a named operation that accepts zero or more inputs and returns a value. The inputs are called arguments. An argument can be a column, literal value, or larger expression.

Functions can calculate numbers, summarize rows, transform text, work with dates and times, handle missing values, and control how query output is displayed.

FUNCTION_NAME(argument1, argument2, ...)

For example, this scalar function converts each product name to uppercase:

SELECT UPPER(product_name) AS uppercase_name
FROM products;

Functions commonly appear in SELECT expressions. Depending on the database and the query, they may also appear in WHERE, HAVING, ORDER BY, and GROUP BY. Use them where their result is appropriate, and remember that function names and syntax can vary by database.

Aggregate functions and scalar functions

SQL functions are commonly divided into two categories: aggregate functions and scalar functions. An aggregate function is also called a set function.

• Aggregate functions process multiple rows and return one result for the complete set or one result for each group.
• Scalar functions process one value, row, or expression at a time and usually return one value for every selected row.

For example, an aggregate query can produce one average for an entire table:

SELECT AVG(price) AS average_price
FROM products;

A scalar expression produces a calculated value for each selected row:

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

The first query normally returns one row. The second normally returns one result row for every product selected.

Comparison of the two categories

• Aggregate input scope: many rows or one group.
• Aggregate result count: one result for the complete set, or one result per group.
• Scalar input scope: one value or expression at a time.
• Scalar result count: usually one result for each selected row.

Core aggregate functions

Aggregate functions summarize values across multiple rows. The most portable core aggregate functions are AVG(), COUNT(), MAX(), MIN(), and SUM().

AVG()

AVG() returns the average of numeric, non-NULL values.

SELECT AVG(price) AS average_price
FROM products;

The calculation is based on values that are present. Missing values represented by NULL are generally ignored.

COUNT()

COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL.

SELECT
    COUNT(*) AS total_products,
    COUNT(discount_price) AS products_with_discount
FROM products;

If the table has 100 rows but 20 rows have a NULL discount price, the two results are 100 and 80.

SUM()

SUM() adds numeric, non-NULL values.

SELECT SUM(amount) AS total_sales
FROM sales;

MIN() and MAX()

MIN() returns the smallest value, while MAX() returns the greatest value in the input set. They can be used with numeric values, dates, and values that have a meaningful sort order.

SELECT
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

Like AVG() and SUM(), these functions generally ignore NULL values.

One summary row without GROUP BY

When an aggregate query has no GROUP BY, the database normally calculates one result for the complete filtered result set.

SELECT
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

This query returns one summary row containing three calculated columns.

GROUP BY: one result per group

GROUP BY partitions rows into groups. An aggregate is then calculated separately for each group.

SELECT
    category_id,
    SUM(amount) AS category_sales
FROM sales
GROUP BY category_id;

This query returns one row for each distinct category_id. A selected column that is not inside an aggregate generally must be included in the GROUP BY list.

WHERE versus HAVING

WHERE filters individual source rows before grouping. HAVING filters groups after aggregate calculations.

SELECT
    category_id,
    SUM(amount) AS category_sales
FROM sales
WHERE sale_date >= DATE '2026-01-01'
GROUP BY category_id
HAVING SUM(amount) >= 10000;

• WHERE removes sales before they are grouped.
• GROUP BY creates one group per category.
• SUM(amount) calculates each category's total.
• HAVING keeps only categories whose total is at least 10,000.

Filtering rows with WHERE before aggregation is often clearer and can reduce the amount of data that must be grouped.

FIRST() and LAST() warnings

Some database systems or older SQL environments expose FIRST() and LAST() as aggregate-style functions. They are not standard SQL, and their behavior differs between systems.

These names also do not inherently define which row is first or last. A table has no guaranteed natural order. To find an actual earliest or latest row, specify an ordering method and use the database's row-limiting syntax or a window function.

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

The example uses standard-style row limiting supported by some databases. Other systems use a different clause. If several rows share the same date, add a tie-breaking column to ORDER BY, such as order_id.

Common scalar functions

A scalar function returns one value for each input row or expression. Scalar functions can be applied to columns, constants, and calculations.

Converting text case

UPPER() converts text to uppercase, and LOWER() converts text to lowercase. Some systems also provide the names UCASE() and LCASE().

SELECT
    UPPER(customer_name) AS name_upper,
    LOWER(customer_name) AS name_lower
FROM customers;

UPPER() and LOWER() are widely recognized alternatives. Check your database documentation before using UCASE() or LCASE().

Extracting part of a string

Text extraction functions return part of a text value. The starting position and requested length determine which characters are returned. Position numbering is dialect-specific, although many systems start at 1.

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

Some databases use a different form, such as SUBSTRING(product_code, 1, 3), SUBSTR(product_code, 1, 3), or MID(product_code, 1, 3). The function name and parameter order must match your SQL dialect.

Finding text length

Text length functions report the length of a string. Common names include CHAR_LENGTH(), LENGTH(), and LEN().

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

LEN() and LENGTH() can have different treatment of trailing spaces or multibyte characters depending on the database. Use the documented function that matches whether you need character length or another type of length measurement.

Rounding numbers

ROUND(number, decimal_places) rounds a numeric value to the requested number of decimal places.

SELECT
    order_id,
    ROUND(quantity * unit_price, 2) AS line_total
FROM order_items;

Because the argument is an expression, the function calculates the line total first and then rounds it.

Current date and time

CURRENT_TIMESTAMP returns the current date and time in many SQL systems. NOW() is also common, but it is not equally portable and may have database-specific timing semantics.

SELECT CURRENT_TIMESTAMP AS query_time;

Formatting output

FORMAT() can format numbers or dates for display in databases that support it. Its arguments, formatting patterns, return type, and availability vary substantially.

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

Treat this as dialect-dependent syntax. A database may instead provide separate date-formatting and number-formatting functions. When values still need to be sorted or calculated numerically, retain the original numeric or date value and format it in the presentation layer whenever practical.

Nesting and combining scalar functions

Functions can be nested. For example, this converts a name to uppercase and then measures the resulting text:

SELECT
    customer_name,
    CHAR_LENGTH(UPPER(customer_name)) AS uppercase_name_length
FROM customers;

Expressions can also combine functions with arithmetic, concatenation, comparisons, and other SQL expressions.

NULL values and function results

NULL represents a missing, unknown, or inapplicable value. It is not the same as zero, an empty string, or false.

Many scalar functions return NULL when their input is NULL. For example, UPPER(NULL) and ROUND(NULL, 2) typically return NULL.

• COUNT(*) counts every row, including rows containing NULL values.
• COUNT(column) counts only non-NULL values in that column.
• SUM(column), AVG(column), MIN(column), and MAX(column) generally ignore NULL inputs.
• If every relevant input is NULL, an aggregate such as SUM() or AVG() may return NULL.

COALESCE() returns the first non-NULL expression in its argument list. It is useful when a fallback value is part of the intended calculation.

SELECT SUM(COALESCE(discount_amount, 0)) AS total_discount
FROM orders;

Here, each missing discount is explicitly treated as zero before summing. Use a fallback only when it accurately represents the business meaning of the missing data.

Aliases for readable results

An alias is a temporary name assigned to a result column or expression. Use AS to make calculated output easier to understand.

SELECT
    ROUND(quantity * unit_price, 2) AS line_total,
    UPPER(product_name) AS display_name
FROM order_items;

Aliases do not rename the underlying table column. They label the value in the query result.

SQL dialect differences and portability

A SQL dialect is a database system's variation of SQL. Function names, parameter order, return types, date rules, formatting patterns, and NULL behavior can differ.

• UPPER() is a common alternative to UCASE().
• LOWER() is a common alternative to LCASE().
• SUBSTRING() or SUBSTR() can replace MID(), but syntax varies.
• CHAR_LENGTH() or LENGTH() can replace LEN()
• CURRENT_TIMESTAMP is a broadly recognized alternative to NOW().
• FIRST(), LAST(), and FORMAT() are especially dependent on the database system and context.

-- May make ordinary index use more difficult
SELECT *
FROM orders
WHERE UPPER(status) = 'PAID';

SELECT *
FROM orders
WHERE status = 'PAID';

SELECT category_id, SUM(amount) AS category_sales
FROM sales
WHERE sale_date >= DATE '2026-01-01'
GROUP BY category_id;

SELECT FUNCTION_NAME(column_or_expression) AS result_name
FROM table_name;

SELECT grouping_column,
       AGGREGATE_FUNCTION(value_column) AS result_name
FROM table_name
GROUP BY grouping_column;

SELECT grouping_column,
       AGGREGATE_FUNCTION(value_column) AS result_name
FROM table_name
GROUP BY grouping_column
HAVING AGGREGATE_FUNCTION(value_column) condition;