SQL is not only used to retrieve data from a database. It can also calculate values, manipulate text, work with dates, perform mathematical operations, handle NULL values, and analyze data.
SQL provides built-in functions that make these tasks easier.
For example, you can use SQL functions to:
- Calculate the average salary of employees.
- Find the total number of customers.
- Convert text to uppercase or lowercase.
- Calculate the total sales amount.
- Find the highest and lowest product prices.
- Extract parts of a date.
- Replace or combine text.
- Handle missing values.
- Round decimal numbers.
- Calculate totals and averages for reports.
This is where SQL Functions become important.
In this complete guide, you will learn what SQL functions are, different types of SQL functions, aggregate functions, scalar functions, string functions, numeric functions, date and time functions, conversion functions, NULL-handling functions, conditional functions, common examples, and best practices.
What Are SQL Functions?
A SQL function is a built-in or user-defined operation that accepts input values, performs a specific operation, and returns a result.
A simple example is:
SELECT UPPER('hello');
Result:
HELLO
Another example:
SELECT LENGTH('SQL');
Result:
3
SQL functions can be used with:
- Table columns
- Constants
- Expressions
- Conditions
- Calculations
- Other functions
For example:
SELECT UPPER(name)
FROM customers;
This converts each customer’s name to uppercase.
Why Are SQL Functions Important?
Functions allow you to perform operations directly inside SQL queries instead of processing everything in your application code.
For example, instead of retrieving every employee’s salary and calculating the average in your application, you can simply use:
SELECT AVG(salary)
FROM employees;
The database calculates the result for you.
SQL functions are especially useful for:
- Data analysis
- Reports
- Dashboards
- Search
- Data cleaning
- Data transformation
- Financial calculations
- E-commerce applications
- CRM systems
- ERP systems
- Analytics
Types of SQL Functions
SQL functions can broadly be divided into two major categories:
- Aggregate Functions
- Scalar Functions
There are also several commonly used categories of scalar functions, such as:
- String functions
- Numeric functions
- Date and time functions
- Conversion functions
- NULL-handling functions
- Conditional functions
Let’s understand each category.
1. Aggregate Functions
Aggregate functions perform calculations across multiple rows and return a summarized result.
Common aggregate functions include:
COUNT()
SUM()
AVG()
MIN()
MAX()
For example:
SELECT COUNT(*)
FROM employees;
This returns the total number of employees.
COUNT() Function
The COUNT() function counts rows or non-NULL values depending on how it is used.
COUNT(*)
SELECT COUNT(*)
FROM employees;
This counts all rows.
For example, if there are 100 employees:
100
COUNT(column)
SELECT COUNT(email)
FROM employees;
This counts rows where email is not NULL.
Therefore:
COUNT(*) → Counts rows
COUNT(column) → Counts non-NULL values
COUNT(DISTINCT)
You can count unique values using:
SELECT COUNT(DISTINCT department_id)
FROM employees;
This counts the number of different departments represented in the employee table.
SUM() Function
SUM() calculates the total of numeric values.
For example:
SELECT SUM(amount)
FROM orders;
This returns the total order amount.
You can use it for:
- Sales
- Revenue
- Expenses
- Salaries
- Inventory quantities
- Transaction amounts
Example:
SELECT SUM(quantity)
FROM order_items;
AVG() Function
AVG() calculates the average of numeric values.
Example:
SELECT AVG(salary)
FROM employees;
This returns the average employee salary.
You can also use:
SELECT AVG(price)
FROM products;
to calculate the average product price.
MIN() Function
MIN() returns the smallest value.
Example:
SELECT MIN(price)
FROM products;
This returns the lowest product price.
It can also be used with dates:
SELECT MIN(order_date)
FROM orders;
This returns the earliest order date.
MAX() Function
MAX() returns the largest value.
Example:
SELECT MAX(price)
FROM products;
This returns the highest product price.
You can also find the latest date:
SELECT MAX(order_date)
FROM orders;
Aggregate Functions with GROUP BY
Aggregate functions become especially useful with GROUP BY.
For example:
SELECT
department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;
This calculates the average salary for each department.
Another example:
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
This shows the number of employees in each department.
Aggregate Functions with HAVING
You can use HAVING to filter grouped results.
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
This returns only departments containing more than 10 employees.
2. Scalar Functions
Scalar functions generally operate on individual values and return a value for each input row.
For example:
SELECT UPPER(name)
FROM customers;
If the table contains:
| name |
|---|
| Rahul |
| Priya |
| Amit |
The result could be:
| UPPER(name) |
|---|
| RAHUL |
| PRIYA |
| AMIT |
Scalar functions include many categories.
SQL String Functions
String functions are used to manipulate text.
Common string functions include:
UPPER()
LOWER()
LENGTH()
TRIM()
SUBSTRING()
CONCAT()
REPLACE()
Exact function names and syntax can vary between database systems.
UPPER() Function
UPPER() converts text to uppercase.
SELECT UPPER(name)
FROM customers;
Example:
Rahul → RAHUL
Priya → PRIYA
This can be useful for:
- Standardizing data
- Search processing
- Reports
- Data cleaning
LOWER() Function
LOWER() converts text to lowercase.
SELECT LOWER(email)
FROM customers;
For example:
USER@EXAMPLE.COM
becomes:
user@example.com
LENGTH() Function
LENGTH() returns the number of characters or bytes depending on the database system and function semantics.
Example:
SELECT LENGTH('SQL');
Result:
3
You can also use it with a column:
SELECT
name,
LENGTH(name) AS name_length
FROM customers;
TRIM() Function
TRIM() removes unwanted leading and trailing spaces.
Example:
SELECT TRIM(' SQL ');
Result:
SQL
This is useful when cleaning user-entered data.
LTRIM() and RTRIM()
Some SQL systems provide:
LTRIM()
RTRIM()
LTRIM() removes leading spaces.
RTRIM() removes trailing spaces.
Example:
SELECT LTRIM(name)
FROM customers;
and:
SELECT RTRIM(name)
FROM customers;
Function availability and behavior can vary by database.
CONCAT() Function
CONCAT() combines multiple strings.
Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
If:
first_name = Rahul
last_name = Sharma
the result is:
Rahul Sharma
String Concatenation
Different database systems support different string-concatenation syntax.
For example, some systems use:
CONCAT(first_name, ' ', last_name)
PostgreSQL also supports the || operator:
SELECT first_name || ' ' || last_name
FROM employees;
Always check the syntax supported by your database.
SUBSTRING() Function
SUBSTRING() extracts part of a string.
A common form is:
SELECT SUBSTRING(name, 1, 3)
FROM customers;
For a name such as:
Rahul
the result may be:
Rah
The exact syntax varies between SQL database systems.
REPLACE() Function
REPLACE() replaces part of a string with another string.
Example:
SELECT REPLACE(phone, '-', '')
FROM customers;
If the phone number is:
123-456-789
the result becomes:
123456789
This is useful for data cleaning and formatting.
Numeric SQL Functions
Numeric functions are used for mathematical calculations.
Common examples include:
ROUND()
CEIL() / CEILING()
FLOOR()
ABS()
MOD()
POWER()
Not every database uses exactly the same function names or syntax.
ROUND() Function
ROUND() rounds a numeric value.
Example:
SELECT ROUND(125.678, 2);
Result:
125.68
This is commonly used for:
- Prices
- Financial reports
- Percentages
- Calculations
Example:
SELECT
product_name,
ROUND(price, 2) AS rounded_price
FROM products;
CEILING() Function
CEILING() returns the smallest integer greater than or equal to a number.
SELECT CEILING(12.3);
Result:
13
Some databases also support:
CEIL(12.3)
FLOOR() Function
FLOOR() returns the largest integer less than or equal to a number.
SELECT FLOOR(12.9);
Result:
12
ABS() Function
ABS() returns the absolute value of a number.
SELECT ABS(-100);
Result:
100
This is useful when the direction of a difference is not important.
For example:
SELECT ABS(actual_amount - expected_amount)
FROM payments;
MOD() Function
MOD() returns the remainder after division.
Example:
SELECT MOD(10, 3);
Result:
1
It can be useful for identifying even and odd values.
For example:
SELECT *
FROM employees
WHERE MOD(employee_id, 2) = 0;
This can select employees with even IDs.
The exact syntax may differ by database.
POWER() Function
POWER() calculates a number raised to a power.
SELECT POWER(2, 3);
Result:
8
SQL Date and Time Functions
Date and time functions are extremely important in business applications.
They are used for:
- Orders
- Payments
- User registration
- Reports
- Attendance
- Subscriptions
- Delivery tracking
- Logs
- Analytics
Common date/time functions vary considerably by database.
Examples include:
CURRENT_DATE
CURRENT_TIMESTAMP
NOW()
EXTRACT()
DATE_PART()
DATE_ADD()
DATE_SUB()
Not all of these are available in every SQL database.
CURRENT_DATE
CURRENT_DATE returns the current date in databases that support this standard-style expression.
Example:
SELECT CURRENT_DATE;
The result depends on the database server/session date and time zone.
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP returns the current date and time.
SELECT CURRENT_TIMESTAMP;
This is useful for timestamps and audit information.
NOW()
Some databases, such as MySQL and PostgreSQL, support:
SELECT NOW();
This returns the current date and time.
Because date/time functions differ between database systems, always check the documentation for your chosen database.
Extracting Parts of a Date
You may need to extract:
- Year
- Month
- Day
- Hour
- Minute
- Second
A standard-style approach supported by many databases is:
SELECT EXTRACT(YEAR FROM order_date)
FROM orders;
For the month:
SELECT EXTRACT(MONTH FROM order_date)
FROM orders;
The exact supported syntax depends on the SQL database.
Date Difference
You may want to calculate the difference between two dates.
For example:
SELECT
order_id,
order_date,
delivery_date
FROM orders;
The actual date-difference function differs substantially between MySQL, PostgreSQL, SQL Server, Oracle, and other systems.
For example, SQL Server provides functions such as:
DATEDIFF()
while MySQL has:
DATEDIFF()
and PostgreSQL commonly uses date subtraction or interval operations.
Therefore, date calculations should always be written according to the target database.
SQL NULL-Handling Functions
NULL represents a missing or unknown value.
SQL provides functions and expressions to handle NULL values.
Common examples include:
COALESCE()
NULLIF()
Some database systems also provide proprietary functions such as:
IFNULL()
ISNULL()
NVL()
These are not interchangeable across all databases.
COALESCE() Function
COALESCE() returns the first non-NULL value.
Example:
SELECT
name,
COALESCE(phone, 'Not Available') AS phone
FROM customers;
If phone is NULL, the query returns:
Not Available
Otherwise, it returns the actual phone number.
Multiple Values with COALESCE()
COALESCE() can accept multiple expressions.
SELECT COALESCE(phone, email, 'No Contact Information')
FROM customers;
SQL checks the values from left to right.
Conceptually:
phone
↓
if NULL
↓
email
↓
if NULL
↓
No Contact Information
NULLIF() Function
NULLIF() returns NULL when two expressions are equal.
Example:
SELECT NULLIF(10, 10);
Result:
NULL
If they are different:
SELECT NULLIF(10, 20);
Result:
10
One common use is avoiding division by zero:
SELECT total_sales / NULLIF(order_count, 0)
FROM sales_summary;
If order_count is zero, NULLIF() returns NULL instead of zero, helping prevent a division-by-zero error in systems where such an expression would otherwise fail.
SQL Conditional Functions
SQL also provides conditional expressions.
The most important is:
CASE
CASE allows you to implement conditional logic.
Example:
SELECT
name,
salary,
CASE
WHEN salary >= 100000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;
Result:
| name | salary | salary_level |
|---|---|---|
| Rahul | 120000 | High |
| Priya | 70000 | Medium |
| Amit | 30000 | Low |
CASE Expression
CASE is technically a SQL expression rather than a conventional function, but it is commonly discussed alongside SQL functions because it performs conditional transformations.
A general form is:
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
It can be used in:
SELECTWHEREORDER BYGROUP BYHAVINGUPDATE
SQL Functions with WHERE
Functions can be used in the WHERE clause.
For example:
SELECT *
FROM customers
WHERE LOWER(city) = 'jaipur';
This allows case-normalized comparison.
However, applying a function to an indexed column can sometimes prevent efficient index usage, depending on the database and query. For performance-sensitive searches, consider database-supported functional indexes or appropriately normalized data.
SQL Functions with ORDER BY
Functions can also be used for sorting.
Example:
SELECT name
FROM customers
ORDER BY LOWER(name);
This can provide case-normalized sorting.
Another example:
SELECT
product_name,
price
FROM products
ORDER BY ROUND(price, 0) DESC;
SQL Functions with GROUP BY
Functions can be used to create grouping expressions.
For example:
SELECT
LOWER(city) AS normalized_city,
COUNT(*) AS total_customers
FROM customers
GROUP BY LOWER(city);
This can group values after normalizing their case.
SQL Functions with HAVING
Aggregate functions are commonly used with HAVING.
SELECT
department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;
This returns departments whose average salary exceeds 60,000.
SQL Functions with JOIN
Functions can also be used with joined data.
SELECT
UPPER(c.name) AS customer_name,
o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Here, UPPER() is applied to the customer’s name after the tables are joined.
Nested SQL Functions
One function can be used inside another function.
For example:
SELECT UPPER(TRIM(name))
FROM customers;
The database conceptually:
- Removes leading/trailing spaces.
- Converts the remaining text to uppercase.
Another example:
SELECT ROUND(AVG(price), 2)
FROM products;
Here:
AVG()calculates the average.ROUND()rounds the result to two decimal places.
Nested functions are powerful, but overly complicated expressions can become difficult to read.
Aggregate Functions vs Scalar Functions
Understanding the difference is important.
Aggregate Function
Works across multiple rows.
Example:
SELECT AVG(salary)
FROM employees;
One result is produced for the selected set.
Scalar Function
Generally operates on an individual value.
Example:
SELECT UPPER(name)
FROM employees;
A value is produced for each row.
Simple Difference
Aggregate → Many rows → Summary result
Scalar → Individual value → Transformed result
SQL Functions and NULL Values
Many SQL functions have special behavior when given NULL.
For example:
SELECT UPPER(NULL);
typically returns:
NULL
Aggregate functions also have specific NULL behavior.
For example:
SELECT AVG(salary)
FROM employees;
normally ignores NULL salary values.
Similarly:
COUNT(salary)
counts only non-NULL salary values, while:
COUNT(*)
counts rows.
Understanding NULL behavior is extremely important when writing reliable SQL.
SQL Function Examples for E-Commerce
Functions are heavily used in e-commerce systems.
Calculate Total Revenue
SELECT SUM(amount) AS total_revenue
FROM orders;
Average Order Value
SELECT AVG(amount) AS average_order_value
FROM orders;
Most Expensive Product
SELECT MAX(price) AS highest_price
FROM products;
Cheapest Product
SELECT MIN(price) AS lowest_price
FROM products;
Number of Orders
SELECT COUNT(*) AS total_orders
FROM orders;
SQL Functions for Customer Management
You can use functions to format customer information.
SELECT
UPPER(first_name) AS first_name,
LOWER(email) AS email
FROM customers;
You can also create a full name:
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;
SQL Functions for Employee Reports
Calculate salary statistics:
SELECT
COUNT(*) AS employees,
AVG(salary) AS average_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary,
SUM(salary) AS total_salary
FROM employees;
This single query can provide several useful metrics.
SQL Functions for Reporting
Functions make reporting much easier.
For example:
SELECT
department_id,
COUNT(*) AS employee_count,
ROUND(AVG(salary), 2) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department_id
ORDER BY average_salary DESC;
This creates a department-level salary report.
SQL Function Performance
Functions are useful, but using them incorrectly can affect query performance.
Consider:
SELECT *
FROM customers
WHERE LOWER(email) = 'user@example.com';
If email has a normal index, applying LOWER() to the column may prevent the database from using that index efficiently.
Depending on the database, possible solutions include:
- Functional/expression indexes
- Case-insensitive collations
- Normalized stored values
- Database-specific indexing features
Always inspect the execution plan for performance-critical queries.
Built-In Functions vs User-Defined Functions
Most databases provide many built-in functions.
Examples include:
COUNT()
SUM()
AVG()
UPPER()
LOWER()
ROUND()
COALESCE()
Some database systems also allow developers to create User-Defined Functions (UDFs).
A UDF lets you package reusable business logic inside the database.
The syntax differs significantly between database systems.
For example, database-specific implementations may use constructs such as:
CREATE FUNCTION ...
The exact syntax depends on whether you are using PostgreSQL, MySQL, SQL Server, Oracle, or another system.
User-Defined Function Example
A simplified example might look like:
CREATE FUNCTION calculate_discount(price DECIMAL)
RETURNS DECIMAL
...
The function could then be used to calculate a discount.
However, UDF support, syntax, language, determinism, and performance characteristics vary significantly between database platforms.
SQL Functions by Category
Here is a useful overview:
| Category | Common Functions |
|---|---|
| Aggregate | COUNT(), SUM(), AVG(), MIN(), MAX() |
| String | UPPER(), LOWER(), LENGTH(), TRIM(), CONCAT(), REPLACE() |
| Numeric | ROUND(), CEILING(), FLOOR(), ABS(), MOD(), POWER() |
| Date/Time | CURRENT_DATE, CURRENT_TIMESTAMP, NOW(), EXTRACT() |
| NULL Handling | COALESCE(), NULLIF() |
| Conditional | CASE |
| Conversion | CAST(), CONVERT() and database-specific alternatives |
| User Defined | Database-specific CREATE FUNCTION |
SQL CAST() Function
CAST() is used to convert a value from one data type to another.
Example:
SELECT CAST('100' AS INTEGER);
This converts the text value '100' into an integer.
Another example:
SELECT CAST(price AS DECIMAL(10, 2))
FROM products;
The exact supported data types and syntax depend on the database.
SQL CONVERT()
Some database systems provide a CONVERT() function.
For example, SQL Server commonly uses:
SELECT CONVERT(INT, '100');
This is database-specific.
For portable SQL, CAST() is often the more standard-style option, although exact portability still depends on the target data type and database.
Common SQL Function Mistakes
Mistake 1: Confusing COUNT(*) and COUNT(column)
These are not always equivalent.
COUNT(*)
counts rows.
COUNT(email)
counts non-NULL email values.
Mistake 2: Ignoring NULL Values
Consider:
SELECT AVG(salary)
FROM employees;
NULL salaries are generally ignored.
If your business logic requires treating missing salaries as zero, you may need:
SELECT AVG(COALESCE(salary, 0))
FROM employees;
But be careful: this changes the meaning of the calculation. It treats missing salaries as actual zero values rather than excluding them.
Mistake 3: Applying Functions to Indexed Columns
For example:
WHERE LOWER(email) = 'user@example.com'
may affect index usage.
For large tables, investigate the execution plan and consider an appropriate index or data model.
Mistake 4: Using Database-Specific Functions Without Checking Compatibility
For example, date and string functions can differ between:
- MySQL
- PostgreSQL
- SQL Server
- Oracle
- SQLite
Always check the documentation for your database.
Best Practices for SQL Functions
1. Use Functions for Data Processing
Let the database perform calculations that naturally belong in the query.
For example:
SELECT SUM(amount)
FROM orders;
is preferable to transferring thousands of rows to the application only to calculate the total there.
2. Use Meaningful Aliases
Instead of:
SELECT AVG(salary)
FROM employees;
you can write:
SELECT AVG(salary) AS average_salary
FROM employees;
This makes the result easier to understand.
3. Be Careful with NULL
Use:
COALESCE()
when you intentionally need a fallback value.
4. Avoid Overly Complex Nested Functions
This:
SELECT ROUND(AVG(COALESCE(price, 0)), 2)
FROM products;
may be perfectly valid, but make sure every transformation is necessary and that its NULL behavior matches your business requirement.
5. Consider Performance
Functions can sometimes prevent indexes from being used efficiently when applied directly to indexed columns.
For performance-critical queries, use:
EXPLAIN
or your database’s equivalent execution-plan tool.
SQL Functions Cheat Sheet
Aggregate Functions
COUNT()
SUM()
AVG()
MIN()
MAX()
String Functions
UPPER()
LOWER()
LENGTH()
TRIM()
CONCAT()
SUBSTRING()
REPLACE()
Numeric Functions
ROUND()
CEILING()
FLOOR()
ABS()
MOD()
POWER()
NULL Functions
COALESCE()
NULLIF()
Date/Time
CURRENT_DATE
CURRENT_TIMESTAMP
NOW()
EXTRACT()
Conversion
CAST()
CONVERT()
Conditional
CASE
Frequently Asked Questions
What are SQL functions?
SQL functions are built-in or user-defined operations that process values and return results.
What are the main types of SQL functions?
The two broad categories are:
- Aggregate functions
- Scalar functions
Scalar functions can further include string, numeric, date/time, conversion, and NULL-handling functions.
What is an aggregate function?
An aggregate function processes multiple rows and returns a summarized result.
Examples:
COUNT()
SUM()
AVG()
MIN()
MAX()
What is a scalar function?
A scalar function generally processes an individual value and returns a value for each input row.
Examples include:
UPPER()
LOWER()
ROUND()
TRIM()
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows.
COUNT(column) counts non-NULL values in that column.
What is COALESCE() used for?
COALESCE() returns the first non-NULL expression.
Example:
SELECT COALESCE(phone, 'Not Available')
FROM customers;
Can SQL functions be used with WHERE?
Yes.
SELECT *
FROM customers
WHERE LOWER(city) = 'jaipur';
However, using functions on indexed columns can affect performance depending on the database.
Can SQL functions be used with GROUP BY?
Yes.
For example:
SELECT
LOWER(city),
COUNT(*)
FROM customers
GROUP BY LOWER(city);
Can multiple SQL functions be combined?
Yes.
For example:
SELECT ROUND(AVG(price), 2)
FROM products;
Here, AVG() calculates the average and ROUND() rounds the result.
Conclusion
SQL functions are essential for working efficiently with database data.
They allow you to perform calculations, transform values, clean data, handle missing values, work with dates, manipulate strings, and generate meaningful reports directly inside SQL queries.
The most important functions to remember are:
COUNT()for counting rows or values.SUM()for totals.AVG()for averages.MIN()for minimum values.MAX()for maximum values.UPPER()andLOWER()for text conversion.TRIM()for removing surrounding spaces.CONCAT()for combining text.ROUND()for rounding numbers.ABS()for absolute values.COALESCE()for handling NULL values.NULLIF()for converting matching values to NULL.CAST()for data type conversion.CASEfor conditional logic.- Date/time functions for working with timestamps and dates.
One important thing to remember is that SQL functions are not completely identical across all database systems. MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and other databases can have different function names, arguments, return types, and date-handling syntax.
A useful SQL learning path is:
SELECT → WHERE → JOIN → GROUP BY → HAVING → ORDER BY → Subqueries → Functions → CTEs → Window Functions
Once you understand SQL functions, you can start writing much more powerful queries for data analysis, reporting, e-commerce, CRM, ERP, finance, dashboards, and business applications.
