MySQL online course

MySQL Functions: String, Date, and Aggregate Functions

Learn how MySQL functions transform text, calculate dates, summarize rows, handle NULL values, and work with WHERE, GROUP BY, and HAVING.

MySQL functions are built-in SQL operations that receive one or more values and return a result. They allow a query to manipulate, format, calculate, compare, and summarize data inside the database.

A function call follows this general form:

FUNCTION_NAME(argument1, argument2, ...)

An argument is a value supplied to a function. It can be a column reference, a literal value, or another expression. For example, last_name is a column, 'Ada' is a string literal, and CONCAT(first_name, ' ', last_name) is a function call.

A function is different from a SQL keyword such as SELECT or WHERE. Functions can appear in expressions in SELECT, and often in WHERE, ORDER BY, GROUP BY, and HAVING.

Categories of MySQL Functions

The three categories in this lesson work at different levels. String and date functions usually transform one row's value at a time. Aggregate functions summarize values from multiple rows.

String Functions

String functions operate on text values such as customer names, product descriptions, email addresses, and labels. Their result is usually text, except functions such as LOCATE, which returns a numeric position.

CONCAT: Join Text Values

CONCAT combines its arguments into one string. Add literal spaces or punctuation explicitly when they are needed in the result.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

The AS full_name portion assigns a column alias, a temporary name for the calculated result. For example, first name Ada and last name Lovelace produce Ada Lovelace.

By default, CONCAT returns NULL if any argument is NULL. A missing last name can therefore make the entire display name NULL. Handle missing values explicitly when necessary, using an appropriate NULL-handling expression such as COALESCE.

REPLACE: Substitute Text

REPLACE(string, search, replacement) returns a copy of a string with matching occurrences replaced.

SELECT REPLACE(description, 'old', 'new') AS updated_description
FROM products;

This is useful for text cleanup, such as changing an outdated abbreviation or word in a product description. If the source string is NULL, the result is NULL. If the search text is not found, the original string is returned.

FORMAT: Prepare a Number for Display

FORMAT(number, decimal_places) returns a formatted textual representation of a number. It rounds to the requested number of decimal places and includes grouping separators according to MySQL's formatting rules.

SELECT FORMAT(total_amount, 2) AS displayed_amount
FROM orders;

A value such as 12345.6 can be displayed with two decimal places and grouping separators. The result is intended for presentation, not further arithmetic. Keep total_amount numeric for calculations, and apply FORMAT in the final reporting layer or final query output. A NULL number produces NULL.

LOCATE: Find a Substring Position

LOCATE(substring, string) returns the starting position of a substring. MySQL positions characters starting at 1, not 0.

SELECT email,
       LOCATE('@', email) AS at_position
FROM customers;

This can find the separator in an email address or a delimiter in a code. If the substring is not found, LOCATE returns 0. If an input is NULL, the result is NULL.

Date and Time Functions

Date functions work with temporal values such as DATE and DATETIME. Valid values and the order of arguments matter when extracting dates or doing date arithmetic.

DATE: Extract the Calendar Date

DATE(date_time_value) removes the time portion from a date-time expression and returns the date portion.

SELECT DATE(created_at) AS created_date
FROM orders;

For example, a timestamp such as 2026-08-18 14:35:00 is reported as 2026-08-18. A NULL input returns NULL.

DATEDIFF: Calculate Days Between Dates

DATEDIFF(date1, date2) returns the number of days in date1 - date2. The time portions are not used for this day difference.

SELECT DATEDIFF(delivered_date, ordered_date) AS delivery_days
FROM orders;

Putting the delivery date first gives elapsed days from ordering to delivery. Reversing the arguments produces a negative result. Invalid or NULL temporal inputs can produce an unexpected result or NULL, so validate the data and argument order.

DATE_ADD: Add an Interval

An interval is a quantity paired with a time unit. Use the form DATE_ADD(date_value, INTERVAL quantity unit).

SELECT DATE_ADD(invoice_date, INTERVAL 30 DAY) AS due_date
FROM invoices;

Common units include DAY, MONTH, and YEAR.

SELECT DATE_ADD(start_date, INTERVAL 2 MONTH) AS review_date,
       DATE_ADD(birth_date, INTERVAL 1 YEAR) AS next_anniversary
FROM records;

Use valid date or date-time values and the required INTERVAL quantity unit syntax. A NULL input normally produces NULL. Invalid interval syntax, quantity, or unit can cause an error.

For more date operations, see MySQL date functions.

Aggregate Functions

An aggregate function processes a set of rows and returns one summary value. Without grouping, the set can be the entire table or the rows remaining after a WHERE filter.

Core Aggregate Functions

• AVG(column): returns the arithmetic mean of non-NULL numeric values.
• COUNT(*): counts rows, including rows containing NULL values.
• COUNT(column): counts non-NULL values in that column.
• SUM(column): adds non-NULL numeric values.
• MIN(column): returns the smallest value in the set.
• MAX(column): returns the largest value in the set.

Most aggregates ignore NULL values. If no usable values remain, results such as AVG and SUM can be NULL. COUNT(*) is different because it counts rows regardless of whether individual columns are NULL.

SELECT COUNT(*) AS order_count,
       COUNT(total_amount) AS orders_with_amount,
       SUM(total_amount) AS total_sales,
       AVG(total_amount) AS average_order,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders;

This query returns one row containing overall order summaries. Use COUNT(*) when you need the number of rows, and COUNT(column_name) when you need the number of rows with a value in that column.

GROUP BY: Summarize Each Category

GROUP BY divides rows into groups. The aggregate is then calculated separately for each group.

SELECT category_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS category_sales,
       AVG(total_amount) AS average_order
FROM orders
GROUP BY category_id;

Every selected expression should either be aggregated or identify the grouping. Selecting a non-aggregated column that is not included in GROUP BY can cause an error or unclear results, depending on the SQL mode.

HAVING: Filter Aggregate Results

HAVING filters groups after aggregate calculations. It is the appropriate clause for conditions such as “only categories whose total sales exceed 1,000.”

SELECT category_id,
       SUM(total_amount) AS category_sales
FROM orders
GROUP BY category_id
HAVING SUM(total_amount) > 1000;

See MySQL aggregate functions for deeper coverage of summaries and grouping.

WHERE Versus HAVING

SELECT category_id,
       SUM(total_amount) AS paid_sales
FROM orders
WHERE status = 'paid'
GROUP BY category_id
HAVING SUM(total_amount) > 1000;

Here, WHERE removes unpaid rows before the groups are formed. HAVING then removes groups whose paid total does not exceed the threshold. An aggregate condition in WHERE is generally invalid because the aggregate result does not exist until after row filtering and grouping.

Using Functions in Query Expressions

Functions are commonly used as calculated columns and given clear aliases.

SELECT order_id,
       DATE(created_at) AS order_date,
       FORMAT(total_amount, 2) AS displayed_total,
       DATE_ADD(DATE(created_at), INTERVAL 30 DAY) AS review_date
FROM orders;

This combines row-level date functions and display formatting. The output is useful for a report, but the formatted amount should not be used for later numeric calculations. Calculate with total_amount, then format the final result.

Functions can also be used in sorting or filtering, when appropriate:

SELECT email, LOCATE('@', email) AS separator_position
FROM customers
ORDER BY separator_position;

Aliases make computed columns easier to read. See SQL aliases for more examples.

NULL and Function Results

NULL represents a missing or unknown value; it is not the same as zero or an empty string. Many functions return NULL when a required input is NULL. This can affect display expressions, date calculations, and aggregates.

• Check whether columns can contain NULL before combining or calculating values.
• Remember that COUNT(column) excludes NULL, while COUNT(*) counts the row.
• Do not assume a missing numeric value is zero unless that matches the data meaning.
• Use NULL-handling expressions when a fallback value is appropriate.

Query Design and Performance Considerations

• Choose functions according to the column's data type: use string functions for text, date functions for temporal values, and aggregates for sets of rows.
• Separate display formatting from data calculations. FORMAT returns text, so retain the original numeric value for arithmetic, sorting, and further calculations.
• Wrapping an indexed column in a function inside a filter can prevent efficient index use. For example, filtering with DATE(created_at) = '2026-08-18' may be less index-friendly than filtering the original column with an appropriate range.
• Filter individual records with WHERE before grouping, and filter calculated group results with HAVING.
• Use aliases to make reports and grouped results understandable.

Troubleshooting Common Problems

CONCAT Returns NULL

One of the arguments is probably NULL. Since NULL can propagate through the expression, inspect the source columns and provide a suitable fallback where missing text is acceptable.

DATEDIFF Is Negative or Unexpected

Check the order. MySQL calculates the first argument minus the second: DATEDIFF(end_date, start_date) gives a positive elapsed-day result when the end date follows the start date.

DATE_ADD Has a Syntax Error

Use the required structure, including the INTERVAL keyword and unit:

DATE_ADD(invoice_date, INTERVAL 30 DAY)

Check that the quantity and unit are valid.

COUNT(column) Is Lower Than the Row Count

COUNT(column) excludes rows where that column is NULL. Compare it with COUNT(*) to distinguish total rows from rows containing a value.

A Grouped Query Fails

Review the SELECT list. Every selected item should be an aggregate expression or appear in GROUP BY. Also ensure that the grouping column identifies the summary you intend to produce.

An Aggregate Condition in WHERE Causes an Error

Move the condition to HAVING. WHERE filters rows before aggregation; HAVING filters groups after aggregation.

A Formatted Number Fails in Arithmetic

FORMAT is for presentation and returns formatted text. Perform arithmetic on the original numeric column, then format the final value.

Exam- and Practice-Relevant Notes

• Recognize the function-call pattern: a function name followed by parentheses and arguments.
• Know that string and date functions usually produce a result for each input row, while aggregates summarize multiple rows.
• Remember that DATEDIFF(a, b) means a - b in days.
• Remember the distinction between COUNT(*) and COUNT(column).
• Use GROUP BY for one aggregate result per category and HAVING to filter those category results.
• Keep numeric values numeric until display formatting is required.

Summary

MySQL functions let a query transform individual values and summarize groups of rows. Use CONCAT, REPLACE, FORMAT, and LOCATE for text and display work; use DATE, DATEDIFF, and DATE_ADD for temporal calculations; and use AVG, COUNT, SUM, MIN, and MAX for summaries. Account for NULL, name calculated results with aliases, filter rows with WHERE, and filter aggregate groups with HAVING.