# Common Functions List

*These examples use columns from the UK Tech Data dataset to illustrate how these functions can be used.*

###
**Basic results manipulation**:

You can sort the results table and limit the number of results which are returned by placing the following instructions after the applicable column name.

**sort [TableName.ColumnName] asc / desc**

*Company.name, Company.total_funding SORT Company.total_funding ASC*

*Company.name, Company.total_funding SORT Company.total_funding DESC*

Sort the results table in ascending or descending order by 1) typing SORT, 2) repeating the tablename.columnname and 3) putting ASC or DESC after the column.

You can also click on the results table and sort the column by clicking on the column heading.

**LIMIT 5**

*Company.name, Company.total_funding desc LIMIT 5*

Limit reduces the number of rows returned by a query to the specified number, e.g. 5.

You can also click on **Limit** above the results table to reduce the number of results. The default is to limit the results to 10 rows. This can be adjusted by changing the number 10 to the desired number of rows.

**distinct**

*DISTINCT Industry.industry*

Use distinct before the column name to return only distinct (different) values.

**Distinct** can also be used in conjunction with many other functions

(e.g. **count(distinct ), sum(distinct ), avg(distinct )**

**Comparison**

When you want to filter for certain values, you can use the comparison methods below.

**<** Less than (a numerical/date-time value) *e.g. Company.profit < 100000*

**<=** Less than or equal to *e.g. Company.profit <= 100000*

**>** More than *e.g. Company.profit > 100000*

**>=** More than or equal to *e.g. Company.profit >= 100000*

**==** Is equal to (a numerical/date-time/string value) *e.g. Company.profit = 100000* *e.g.2 Company.hq_city = ‘London’*

**!=** Is not equal to *e.g. Company.profit != 100000* *e.g.2 Company.hq_city != ‘London’*

**contains(ColumnName,‘example fragment’)** Text contains fragment *e.g. contains(Industry.industry,‘tech’)*

By default, all filters are inclusive (like **AND** logic if you are familiar with this) meaning a row has to pass all the filter conditions to be included.

You can use the word **OR** (in capitals) between different conditions if you want rows to be included if they meet one or more of any of the conditions.

For example, *count(Industry.company.ID), Company.profit > 100000, Industry.industry = ‘fintech’* will return the number of companies which both have a profit over 100000 *and* which are in the fintech sector.

If you specify *count(Industry.company.ID), Company.profit > 100000 OR Industry.industry = ‘fintech’*, the result will be the number of companies which *either* have a profit over 100000 *or* which are in the fintech sector.

**Aggregates**

Use the functions below to perform a calculation on a set of numerical values.

**sum**

*sum(Company.total_funding)*

Calculate the sum of all values in the column

**count**

*Industry.industry, count(Industry.company_id)*

Counts the number of values in a column (excluding nulls) or the number of records which meet specified filter criteria

**average**

*Industry.industry, avg(Company.total_funding)*

Calculates average (mean) from all selected values

**median**

*Industry.industry, median(Round.round_valuation)*

Returns the largest value from the selected values

**max**

*max(Company.total_funding)*

Returns the largest value from the selected values

**min**

*min(Company.total_funding)*

Returns the smallest value from the selected values

**stdev**

*stdev(Round.round_valuation)*

Returns the standard deviation of selected values

**variance**

*Industry.industry, variance(Round.round_valuation)*

Returns the statistical variance of the selected values

**corr(numerical column 1, numerical column 2)**

*corr(Company.valuation, Company.total_funding)*

Returns the Pearson coefficient of correlation of a set of number pairs. For each number pair, the first number is the dependent variable and the second number is the independent variable. The return result is between -1 and 1. A result of 0 indicates no correlation.

**round**

*Industry.industry, round(avg(Company.total_funding),2)*

Rounds the input X by Y decimal places as ROUND(X,Y). By default Y is 0.

**power**(base,exponent)

(Company.name, power(Company.total_funding,2)

Power (base) to the power of (exponent)

**sqrt**

*Company.name, sqrt(Company.total_funding)*

Returns the square root of a value

**Date-Time Functions**

Use the functions below with date-time values.

**day**

*day(Company.valuation_date)*

Returns the day from a date-time value in the format YYYY-MM-DD

**day_of_month( )**

*day_of_month(Company.valuation_date)*

Returns the day from a date-time value as an integer from 1 to 31

**day_of_week**

*day_of_week(Company.valuation_date)*

Returns the weekday from a date-time value as an integer from 0 to 6, where 0 is Sunday and 6 is Saturday

**day_of_year**

*day_of_year(Company.valuation_date)*

Returns the day from a date-time value as an integer from 1 to 366

**month**

*month(Company.valuation_date)*

Returns the month from a date-time value in the format YYYY-MM

**month_of_year**

*month_of_year(Company.valuation_date)*

Returns the month from a date-time value as an integer from 1 to 12 (where 1 is January).

**week_of_year**

*week_of_year(Company.valuation_date)*

Returns the week from a date-time value as an integer from 00 to 53

**year**

*year(Company.valuation_date)*

Returns the year from a date-time value in the format YYYY

**datetime**

*date(Company.valuation_date)*

Returns date-time values in the format YYYY-MM-DD hh:mm:ss

**time**

*time(Company.valuation_date)*

Returns date-time values in the format hh:mm:ss

**hour_of_day**

*hour_of_day(Company.valuation_date)*

Returns the integer hour of day

**minute_of_day**

*minute_of_day(Company.valuation_date)*

Returns a datetime value in the format H:M

**minute_of_hour**

*minute_of_hour(Company.valuation_date)*

Returns the integer minute of the hour

**second_of_minute**

*second_of_minute(Company.valuation_date)*

Returns the integer second of the minute.

**String Functions**

Use the functions below with string columns (e.g. text).

Use these functions by writing the function name before the applicable column:

FUNCTION(‘string’ or column)

**lower**

*lower(Company.name)*

Converts all characters to lowercase.

**upper**

*upper(Company.name)*

Converts all character to uppercase.

**substr(string,start,length)**

*substr(Company.name,1,4)*

Returns a substring of input string that begins with the start-th character and which is length characters long. If length is omitted then substr(string,start) returns all characters.

**replace(string,toFind,toReplace)**

*replace(Company.name,‘M’,‘m’)*

Returns string with any substring toFind replaced by toReplace.

**length**

*length(Company.name)*

Returns the length of the value.

**concat**

*concat(Company.name," ",Company.hq_city)*

Returns a concanated string.

**Arithmetic**

You can use the following arithmetic calculations with numerical values.

`+ - * /`

Normal arithmetic operations

`**`

Exponentiate

`%`

Modulo (find the remainder after division of one number by another)