SQL - Useful Functions

SQL - Essential Built-in Functions

SQL provides a rich library of built-in functions to perform calculations, manipulate data, and format results directly within your queries. Understanding these functions is key to writing efficient and powerful SQL. They can be broadly categorized into several groups.

1. Aggregate Functions

Aggregate functions operate on a set of rows to return a single summary value. They are most commonly used with the GROUP BY clause.

• COUNT(column | *) - Counts the number of rows. COUNT(*) counts all rows, while COUNT(column) counts non-NULL values in that column.
• SUM(column) - Calculates the sum of all numeric values in a column.
• AVG(column) - Calculates the average of all numeric values in a column.
• MIN(column) - Finds the minimum value in a column.
• MAX(column) - Finds the maximum value in a column.
• STRING_AGG(column, separator) (PostgreSQL/SQL Server) / GROUP_CONCAT(column) (MySQL) - Concatenates string values from multiple rows into a single string.

2. Window Functions

Window functions are a modern and powerful feature. They perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows; they return a value for each row based on a ‘window’ of data defined by the OVER() clause.

• ROW_NUMBER() - Assigns a unique integer to each row within a partition, starting from 1.
• RANK() / DENSE_RANK() - Assigns a rank to each row based on an ordering. RANK() leaves gaps in ranks after ties, while DENSE_RANK() does not.
• LEAD(column, offset) - Accesses data from a subsequent row in the same result set without a self-join.
• LAG(column, offset) - Accesses data from a previous row in the same result set.
• SUM(column) OVER (…) - Calculates a cumulative sum or a moving total.

3. String Functions

These functions are used to manipulate character strings.

• CONCAT(string1, string2, …) - Joins two or more strings together. The || operator is a common alternative.
• LENGTH(string) / LEN(string) - Returns the number of characters in a string.
• UPPER(string) / LOWER(string) - Converts a string to uppercase or lowercase.
• SUBSTRING(string FROM start FOR length) - Extracts a portion of a string.
• TRIM([BOTH|LEADING|TRAILING] chars FROM string) - Removes leading, trailing, or both-end characters (defaults to whitespace).
• COALESCE(value1, value2, …) - Returns the first non-NULL value in a list. Extremely useful for providing default values.

4. Numeric Functions

These functions perform mathematical operations.

• ROUND(number, decimals) - Rounds a number to a specified number of decimal places.
• CEIL(number) / CEILING(number) - Rounds a number UP to the nearest integer.
• FLOOR(number) - Rounds a number DOWN to the nearest integer.
• ABS(number) - Returns the absolute (non-negative) value of a number.
• RAND() / RANDOM() - Generates a random floating-point number (behavior varies by database).

5. Date and Time Functions

These functions are essential for working with temporal data.

• CURRENT_TIMESTAMP / NOW() - Returns the current date and time of the database server.
• CURRENT_DATE / CURRENT_TIME - Returns the current date or time.
• EXTRACT(part FROM date) - Extracts a part of a date (e.g., YEAR, MONTH, DAY).
• DATE_ADD() / DATE_SUB() (MySQL) or Interval Arithmetic (PostgreSQL) - Add or subtract a time interval from a date. Example: order_date + INTERVAL '30 days'.