SQL | Advanced Functions

SQL provides a variety of advanced functions for performing complex calculations, transformations and aggregations on data. These functions are essential for data analysis, reporting, and efficient database management.

1. Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value.

• SUM(): Calculates the sum of values in a column.
• AVG(): Computes the average of values in a column.
• COUNT(): Returns the number of rows or non-null values in a column.
• MIN(): Finds the minimum value in a column.
• MAX(): Retrieves the maximum value in a column.

Example

SELECT COUNT(*), AVG(Salary), SUM(Salary), MIN(Salary), MAX(Salary) 
FROM Employees;

Output :

Returns count of rows, average, total salary, min salary, and max salary.

2. Conditional Functions

Conditional functions help apply logic inside SQL queries.

• CASE WHEN: Allows conditional logic to be applied in the SELECT statement.
• COALESCE(): Returns the first non-null value in a list.
• NULLIF(): Compares two expressions and returns null if they are equal; otherwise, returns the first expression.

Example

SELECT Name, 
       CASE WHEN Salary > 5000 THEN 'High' 
            ELSE 'Low' END AS Salary_Level
FROM Employees;

Output:

Labels employees as High or Low salary.

3. Mathematical Functions

Mathematical functions are used for numeric calculations. Some commonly used mathematical functions are given below:

• ABS(): Returns the absolute value of a number.
• ROUND(): Rounds a number to a specified number of decimal places.
• POWER(): Raises a number to a specified power.
• SQRT(): Calculates the square root of a number.

Example

SELECT ABS(-15), ROUND(25.678, 2), POWER(2, 3), SQRT(49);

Output :

15, 25.68, 8, 7

4. Advanced Functions in SQL

Beyond aggregates and math, SQL offers system and utility functions for deeper insights.

BIN()

Convert decimal to binary

SELECT BIN(18);

Output:

BINARY()

Convert to binary string

SELECT BINARY "GeeksforGeeks";

Output:

COALESCE()

Returns the first non-null expression in a list

SELECT COALESCE(NULL,NULL,'GeeksforGeeks',NULL,'Geeks');

Output:

CONNECTION_ID()

Returns the unique connection ID for the current connection

SELECT CONNECTION_ID();

Output:

CURRENT_USER()

Returns the user name and hostname for the MySQL account used by the server.

SELECT CURRENT_USER();

Output:

DATABASE()

Returns the name of the default database.

SELECT DATABASE();

Output:

IF()

Returns one value if a condition is TRUE, or another value if a condition is FALSE

SELECT IF(200<500, "YES", "NO");

Output:

LAST_INSERT_ID()

Returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement

SELECT LAST_INSERT_ID();

Output:

NULLIF()

Returns NULL if equal

SELECT NULLIF(115, 115);

Output:

SESSION_USER()

Returns the user name and host name for the current MySQL user

SELECT SESSION_USER();

Output:

SYSTEM_USER()

Returns the user name and host name for the current MySQL user.

SELECT SYSTEM_USER();

Output:

USER()

It returns the user name and host name for the current MySQL user

SELECT USER();

Output:

VERSION()

It returns the version of the MySQL database

SELECT VERSION();

Output: