---
title: "SQL Common Functions"
---
# SQL Common Functions
> Functions are available in most SQL databases, and can provide some helpful utility to any queries you may be calling in Ignition.
This page contains some commonly used SQL functions that some databases contain. The exact functions available and usage depends on the database, so always check your database's documentation for a more complete list of available functions.
## Using Column Values
It is important to understand that when calling these functions, you generally use a column name instead of a static number. For the sake of simplicity, the tables below demonstrate how to use the functions with static values, but they usually are switched for column names, ie:
```sql title="Pseudocode - Passing a Column to a Function"
SELECT SUM(downtime_duration) FROM downtime_events
```
### Example Table
Some of the functions on this table are better demonstrated when used in conjunction with a table (i.e., using the AVG() function with a single value isn't too interesting). Thus, the following table contains sample data that the functions on this page will utilize if necessary.
#### Products Table
| id | product_quantity | product_name | date_added | date_updated | origin_state |
| -- | ---------------- | ------------ | ---------------------------- | ---------------------------- | ------------ |
| 1 | 100 | apples | Mon Jan 29 00:00:00 PST 2018 | Mon Jan 29 12:00:00 PST 2018 | California |
| 2 | 24 | oranges | Mon Feb 13 00:00:00 PST 2017 | Mon Feb 13 09:00:00 PST 2017 | Florida |
| 3 | 56 | grapes | Mon Mar 07 00:00:00 PST 2016 | Mon Mar 07 05:00:00 PST 2016 | California |
## Numeric Functions
| Function | Description | Example | Output |
|---|---|---|---|
| ABS(value) | Returns the absolute value of the passed number or column. | SELECT ABS(-3.5)
| 3.5 |
| AVG(value) | Takes the values of a single numeric column, and returns an average. A WHERE clause may be used in the same statement to filter out some of the rows on the table. | SELECT AVG(product_quantity)FROM products
| 60 |
| CEILING(value) | Returns the next greatest integer value based on the argument provided. Thus, CEILING(10.1 ) would return 11. | SELECT CEILING(10.1)
| 11 |
| COUNT(value) | Returns a row count. Typically takes either a single column, *, or 1. Regardless of which row is passed, the function will return the number of rows on the table that meet the criteria of any WHERE clauses. | SELECT COUNT(*)FROM products
| 3 |
| FLOOR(value) | Returns the next smallest integer value based on the argument provided. Thus, FLOOR(10.9) would return 10. | SELECT FLOOR(10.9)
| 10 |
| MAX(value) | Returns the largest value from the specified column. | SELECT MAX(product_quantity)FROM products
| 100 |
| MIN(value) | Returns the smallest value from the specified column. | SELECT MIN(product_quantity)FROM products
| 24 |
| ROUND(value, decimal_places) | Returns a number rounded to a certain number of decimal places.Takes two parameters. The first is the number to round to, and the second is the number of decimal places to round to. | SELECT ROUND(1.234, 1)
| 1.2 |
| SUM(value) | Takes the value of a single numeric column, and returns the sum. A WHERE clause may be used in the same statement to filter out some of the rows on the table. | SELECT SUM(product_quantity)FROM products
| 180 |
## String Functions
| Function | Description | Example | Output |
|---|---|---|---|
| CONCAT(value1, value 2,....valueN) | Concatenates multiple strings or values. Some databases may require you to convert each value to a string before concatenating. | SELECT CONCAT(product_name, ':', product_quantity)FROM products
| apples:100oranges:24grapes:56 |
| LOWER(value) | Converts a string to lowercase. | SELECT LOWER('MAKE Me smALL') | make me small |
| LTRIM(value) | Removes leading space from a string. | SELECTCT LTRIM(' Take a little off the left') | Take a little off the left |
| REPLACE(orignal_string,target_string, replacement_string) | Searchings a string for a substring (target_string), and replaces the substring with the replacement_string. | SELECT REPLACE('Who is awesome', 'Who is', 'You are') | You are awesome |
| RTRIM(value) | Removes leading space from a string | SELECT RTRIM('Take a little off the right ') | Take a little off the right |
| SUBSTRING(orignal_string,character_index, [length]) | Extracts a substring from another string based on character index. Takes two parameters: the original string, and the character index to start at. An optional third parameter can specify the number of characters to extract.Character index is one-based, so the first character in the string rests at index 1. | SELECT SUBSTRING('This is my string!' , 9, 9) | my string |
| TRIM(value) | Removes both leading and trailing space from a string. | SELECT TRIM(' Trim Both Sides ') | Trim Both Sides |
| UPPER(value) | Converts a string to uppercase. | SELECT UPPER('super size me') | SUPER SIZE ME |
## Date Functions
There are many date and time functions for each database (MySQL, MSSQL, Oracle, etc), but they all vary wildly. These examples work in most databases:
| Function | Description | Example | Output |
|---|---|---|---|
| CURRENT_TIMESTAMP() | Returns the current date and time, as reported by the database. | SELECT CURRENT_TIMESTAMP()
| Returns the current time |
| TIMEDIFF(date1, date2) | Returns a difference between two dates. Assumes thate date1 is the most recent datetime. | SELECT TIMEDIFF(date_updated, date_added)FROM productsWHERE id = 1
| Thu Jan 01 12:00:00 PST 1970 |
## Logic Functions
| Function | Description | Example | Output |
|---|---|---|---|
| COALESCE(value1, value2,...valueN) | Returns the first non-null expression. | SELECT COALESCE(NULL, 'Pick me!')
| Pick me! |
| ISNULL(expression) | Returns true if an expression is NULL. | SELECT ISNULL(NULL)
SELECT ISNULL(14)
| TrueFalse |
| NULLIF(expression1, expression2) | Compares two expressions. If they are equal to each other, then the function returns a NULL. If the two expressions are not equal, the first expression passed to NULLIF() is returned. | SELECT NULLIF(100, 100)
SELECT NULLIF(100, 3)
| NULL100 |
## Group By Clause
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. This way you can find the MIN, MAX, Average, COUNT, etc., for each group of rows. Grouping can also be done for multiple columns, with precedence going in the order that they are listed.
```sql title="Pseudocode - Passing a Column to a Function"
SELECT SUM(column1) FROM table GROUP BY column2
```
#### Example
Here, we are grabbing the sum of product quantity for each origin state.
```sql title="SQL - Simple Group By"
SELECT SUM(product_quantity), origin_state, FROM products_table GROUP BY origin_state
```