Functions

What are functions? How can i use them?

Data Aggregation Functions in SQL

Here you find all data aggregation functions, which are used with the GROUP BY statement within the SELECT command. Theese functions aggregate data or get statistics from groups of rows.

AVG - average

Syntax

Description

    AVG calculates the average of all values in the specifyed column.

    Example:

SELECT AVG(preis)
 FROM torder

Calculates the average of all values of preis.

COUNT - count records and values

Syntax

Description

    COUNT counts the number of records with no NULL-values in the specifyed field or the whole number of records in the table if '*' is used instead of specifying a column.

    Example:

SELECT COUNT(DISTINCT preis)
 FROM torder

Counts all unique values of the column preis of the table torder.

SUM - sum up the values

Syntax

Description

    SUM sums up all values in the specifyed column.

    Example:

SELECT SUM(preis)
 FROM torder

Sums up all values of the column preis.

MIN - minimum value

Syntax

Description

    MIN returns the minimum value of the column.

    Example:

SELECT MIN(preis)
 FROM torder

Returns the minimum value of preis.

MAX - maximum value

Syntax

Description

    MAX returns the maximum value of the selected column.

    Example:

SELECT MAX(preis)
 FROM torder

Returns the maximum value of the column preis.

RANGE - range of values (MAX - MIN)

Syntax

Description

    RANGE calculates the difference between the maximum and the minimum values of the column.

    Example:

SELECT PANGE(preis)
 FROM torder;

Calculates the difference between the biggest and the lowest preis.

STDDEV - standard deviation

Syntax

Description

    STDDEV calculates the standart deviation of the values in the specifyed column. Standart deviation is the square root of variance.

    Example:

SELECT STDDEV(preis)
 FROM torder

Calculate the standart variation of all values of preis.

VARIANCE - variance of values in a column

Syntax

Description

    VARIANCE calculates the variance of the values in the column.

    Example:

SELECT VARIANCE(preis)
 FROM torder

Calculate the variance of all values in the column preis.

Mathematical Functions

Most important mathematical and arithmetical functions, which are implemented in most SQL databases.

SyntaxDescription
ABS(Formula) ABS returns the value istself, when positiv, and (-1)*value, when negativ.
CEIL(Formula) CEIL rounds a value (number with decimals) to the next bigger integer.
HEX(Formula) HEX returns the hex-value of the argument.
FLOOR(Formula) FLOOR rounds a value (number with decimals) to the next smaller integer.
MOD(Formula1, Formula2) MOD returns the rest of an division of two arguments.
ROUND( Formula [, Integer ] ) Rounds the argument to the next floating point number with specifyed number of decimals. If the oprional number of decimals in not given, then the result would be an integer.
SQRT(Formula) Returns the square root of the argument.
TAN(Formula) Returns the tangens of the argument.
TRUNC(Formula) TRUNC rounds the argument to the next smaller integer. In some databases a charakter value is converted to a number, before it is rounded.

Text and Strings

Here you find most important text and string functions, which are supported by most of the sql database systems.

SyntaxDescription
LENGTH(Formula) Returns the length of the string in characters
LEFT( Formula1 , Formula2 ) Returns a number of characters, specifyed by Fomula2, from the left side of the argument Formula1.
RIGHT(Formula1, Formula2) Returns a number of characters, specifyed by Fomula2, from the right side of the argument Formula1.
SUBSTR(Formula1, Formula2, Formula3) Returns a substring from the argument Formula1, which starts at position Formula2 and is Formula3 characters long.
TRIM(Formula) TRIM deletes all spaces form the left and right sides of the argument.
VALUE(Formula) DB2. Converts the string into a numeric value.

Date and Time Functions

Here are the most important date and time funtcions, which are supported by most of sql databases.

SyntaxDescription
CURRENT Returns a DATETIME with current date and time.
DATE(Formula) Convert the string argument into date. In some RDBMS in numeric argument is alowed too.
DAY(Formula) Returns the day from a date-value.
EXTEND(Datetime, [ raram1 TO param2 ] ) Sets the precision of date and datetime values. The first argument is the date to be converted, the second argument defines the precision in form of beginning value and the end value desired.
Example:
SELECT EXTEND( datevalue, MONTH TO YEAR) as time, sum(anz) as orders 
FROM torder 
GROUP BY 1 
Return some order statistics.
MDY(Formula1, Formula2, Formula3) Builds a date from ist components : month, day of the month, year.
MONTH(Datum) Extracts and returns the month from a date.
TODAY Returns current date.
WEEKDAY(datevalue) Calculates and returns the weekday of a date.
YEAR(datevalue) Extracts and returns the year from a date.

Other SQL Functions

Here you find some of the most important helper functions, which are supported by most sql databases.

SyntaxDescription
NVL(Field [, Value ] ) Replaces all NULL-values with the value given in the argument.
DBSERVERNAME Returns the name of the database server.
USER Returns the login name of the currrent user.
SITENAME Returns the name of the current database server.