SQL Functions
From AgileApps Support Wiki
Revision as of 20:37, 11 November 2011 by imported>Aeric
These functions can be used in your SQL statements.
- Legend
-
- [ x ] - Optional (one or none)
- [, x ... ] - Optional additional values, in a comma-separated list
- x | y - Choose one. Curly braces are added when needed: { x | y } ...
- CAPITALIZED - SQL Keyword (case insensitive)
- italicized - Value you supply. (Table names and column names are case-sensitive)
Logical Functions
IF(expr1,expr2,expr3) If expr1 is not zero and not null, return expr2. Otherwise, return expr3. IFNULL(expr1,expr2) If expr1 is not NULL, return expr1; Otherwise, return expr2.
String Functions
CONCAT(str1,str2,...) Concatenates the strings. Returns the result. LENGTH(str) Returns the length of the string, in bytes. (A mult-byte character counts as multiple bytes.)
Numeric Functions
|-
ROUND(expr)
Date Functions
DATE_ADD DATEDIFF CURDATE NOW CURTIME DATE_FORMAT DATE_SUB DATE DAYOFMONTH DAYNAME DAYOFWEEK DAYOFYEAR EXTRACT FROM_UNIXTIME HOUR LAST_DAY MINUTE MONTH MONTHNAME SEC_TO_TIME SECOND STR_TO_DATE SUBTIME TIME_TO_SEC TIME TIMEDIFF TO_DAYS UNIX_TIMESTAMP YEAR
Aggregate Functions
SUM(expr)
SUM(DISTINCT expr)Returns the sum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. AVG(expr)
AVG(DISTINCT expr)Returns the average of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. COUNT(expr) Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement Returns 0 if there were no matching rows. COUNT(*) Returns a count of rows, whether or not they contain NULL values. COUNT(DISTINCT
expr [, expr ...])Returns a count of rows with different non-NULL expr values. MAX(expr)
MAX(DISTINCT expr)Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. MIN(expr)
MIN(DISTINCT expr)Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values. STD(expr) Returns the standard deviation of expr. VARIANCE(expr) Returns the standard variance of expr.