Difference between revisions of "SQL Functions"
From AgileApps Support Wiki
imported>Aeric |
imported>Aeric |
||
| Line 78: | Line 78: | ||
===Aggregate Functions=== | ===Aggregate Functions=== | ||
:{| border="1" cellpadding="5" cellspacing="1" | :{| border="1" cellpadding="5" cellspacing="1" | ||
| '''SUM''' || | | '''SUM(expr)'''<br>'''SUM(DISTINCT expr) || Returns the sum of the values in the expr, or of the distinct values in the expression. Returns NULL if the expression has no values. | ||
|- | |- | ||
| '''AVG''' || | | '''AVG(expr)'''<br>'''AVG(DISTINCT expr) || Returns the average of the values in the expr, or of the distinct values in the expression. Returns NULL if the expression has no values. | ||
|- | |- | ||
| '''COUNT''' || | | '''COUNT''' || | ||
Revision as of 20:18, 11 November 2011
These functions can be used in your SQL statements.
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.)
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 expr, 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 expr, or of the distinct values in the expression. Returns NULL if the expression has no values. COUNT MAX MIN STD VARIANCE ROUND