Grouping Functions:
Grouping functions may include either of the keywords
DISTINCT or ALL.
ALL is the default if neither is specified and uses all
selected rows in the calculation. DISTINCT uses only one row for each value in
the calculation.
Example:
• AVG(ALL
2,2,3,3,4) and AVG(2,2,3,3,4) both return 2.8.
•
AVG(DISTINCT 2,2,3,3,4)
returns 3.
|
Grouping Functions and Parameters
|
Meaning
|
|
AVG(expression)
|
Returns the average of the values in
a set of rows
|
|
COUNT(expression) or COUNT(*)
|
Returns the number of rows in the
set
Note:
If you include an
expression, COUNT returns only the number of rows in which the expression is
not null.
COUNT(*) counts all rows. Since no
HDW table contains nulls, COUNT(expression) and COUNT(*) are equivalent.
|
|
MAX(expression)
|
Returns the largest value from a set
of rows
Note:
See the GREATEST
function if you want the largest of a series of values in a single row.
|
|
MIN(expression)
|
Returns the smallest value from a
set of rows
Note:
See the LEAST
function if you want the smallest of a series of values in a single row.
|
|
SUM(expression)
|
Adds the value for all rows in the
query or for all rows with the same values for columns listed in the GROUP BY
clause
|
|
Numeric Functions and Parameters
|
Meaning
|
|
ABS(number)
|
Removes the sign, if
any, returning a positive value
|
|
GREATEST(value1,
value2, …)
|
Returns the largest of the values in
the list
Note:
This function is
used for multiple values in the same row. See the MAX function if you want the
largest value from a group of rows.
|
|
LEAST(value1,
value2, …)
|
Returns the smallest of the values
in the list
Note:
This function is
used for multiple values in the same
row.
See the MIN
function if you want the smallest
value from a group of
rows
|
|
ROUND(number,
decimal places)
|
Rounds a value to the specified
number of decimal places
|
|
TRUNC(number,
decimal places)
|
Cuts off a value at the specified
number of decimal places
|
|
String Functions and Parameters
|
Meaning
|
|
string || string
|
Concatenates string values
Note:
The equivalent
CONCAT function accepts only two
arguments and is more
confusing in queries.
|
|
INITCAP(string)
|
Converts a string to initial capital
letters
Note:
This function will
convert “a,” “an,” and “the” to “A,”
“An,” and “The.”
|
|
LENGTH(string)
|
Returns the number of
characters in a string
|
|
LOWER(string)
|
Converts a string to
all lowercase characters
|
|
SUBSTR(string,starting value,number
of
characters)
|
Extracts a portion of a string
Note:
If the starting
value is 0, it is treated as 1. If thestarting-value is negative, Oracle
counts backward from the end of the string. If the starting value is positive,
Oracle counts forward from the beginning of the string.
|
|
UPPER(string)
|
Converts a string to
all uppercase characters
|
|
Date Functions and Parameters
|
Meaning
|
|
ADD_MONTHS(date, number ofmonths)
|
Adds the specified number of months
to the date value(subtracts months if the number of months is negative)
Note:
If the result would
be a date beyond the end of the month, Oracle returns the last day of the
resulting month.
|
|
LAST_DAY(date)
|
Returns the last day
of the month that contains the date
|
|
MONTHS_BETWEEN(date1,date2)
|
Returns the difference between two
dates expressed as whole and fractional months
Note:
If date1 is earlier
than date2, the result is negative.
The result also takes into account
time differences between the two values.
|
|
NEXT_DAY(date,day name)
|
Returns the date-time rounded to the
unit specified by the format, or to the nearest day if no format is supplied
Note:
For details on
available formats, see the full description of functions
|
|
SYSDATE
|
Returns the current date-time from
the server where the database is located
|
|
TRUNC(datetime)
|
Removes the time component from a
date-time value
|
|
Conversion Functions and Parameters
|
Meaning
|
|
TO_CHAR(date,format)
|
Converts a date to a
string in the specified format
|
|
TO_CHAR(number,format)
|
Converts a number to a
string in the specified format
|
|
TO_DATE(string,format)
|
Converts a string to a date using
the specified format
Note:
Oracle
automatically converts dates in the standard
format of DD-MON-YYYY.
|
|
TO_NUMBER(string, format)
|
Converts a string to a number using
the optional format if specified
|