Which functions can I use in Count?

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)

Advanced functions

Ternary expressions:

  • if (filter expression) then ’ ’ else ’ ’ Returns the then or else results based on the filter expression. e.g.1 IF 100 > 0 THEN ‘a’ ELSE ‘b’ would return the result ‘a’. e.g.2 Company.name, IF Profit > 0 THEN ‘Profitable’ ELSE ‘Not Profitable’ would return the column of company names and another column stating ‘Profitable’ or ‘Not Profitable’ in each row depending on whether each company meets the Profit > 0 criteria.

Cast functions:

The following cast functions can be used to convert from one data type to another. Note that not all types can be converted into each other.

  • number Converts into a number data type. If a value cannot be converted into a datetime, the result will be null.

List functions:

The following list functions can be used to generate a list of values

  • linspace(start,stop,num) Generates a column of length num, with the first value given by start, and each subsequent value given by the previous value plus (stop-start)/num
  • logspace(start,stop,num) Generates a column of length num, with the first value given by start, and each subsequent value given by the previous value plus (stop-start)/num in log space
  • range(start,stop,step) Generates a column, with the first value given by start, and each subsequent value given by the previous value plus the value step until the value stop is reached.

Mathematical functions:

The following mathematical functions can be used in Count:

floor

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

Rounds down value to the next lowest integer

ceil

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

Rounds up value to the next highest integer

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.

  • abs Calculates the absolute value of an expression
  • acos Calculates the arc-cosine of an expression
  • acosh Calculates the arc-cosh of an expression
  • asin Calculates the arc-sine of an expression
  • asinh Calculates the arc-sinh of an expression
  • atan Calculates the arc-tangent of an expression
  • atan2 Calculates the arc-tangent2 of two expressions
  • atanh Calculates the arc-tanh of an expression
  • cos Calculates the cosine of an expression
  • cosh Calculates the cosh of an expression
  • coth Calculates the coth of an expression
  • degrees Calculates the angle in degrees for an expression specified in radians
  • exp Calculates the exponential of an expression
  • log Calculates the natural logarithm of an expression
  • log10 Calculates the natural logarithm base 10 of an expression
  • radians Calculates the angle in radians for an expression specified in degrees
  • sign Calculates the sign of an expression
  • sine Calculates the sine of an expression
  • sinh Calculates the sinh of an expression
  • sqrt Calculates the square root of an expression
  • tan Calculates the tan of an expression
  • tanh Calculates the tanh of an expression

Additional aggregate functions:

  • group_concat(column) Returns all non-null values in the column concatenated as a string.

Additional string functions:

  • index_of(column,‘substring’) Returns the position of the first occurrence of a substring within another string.

Column Expressions:

  • [‘Expression 1’,‘Expression 2’,…] Generates a column of values by returning each expression within the [ ] as a row. e.g. [‘This’,‘is’,‘an’,‘example.’] would be returned as a column with each word in a new row.