A SERVICE OF

logo

Chapter 5
|
Supported standards 37
Examples
SELECT SUM (Sales_Data.Amount) AS agg FROM Sales_Data
SELECT AVG (Sales_Data.Amount) AS agg FROM Sales_Data
SELECT COUNT (Sales_Data.Amount) AS agg FROM Sales_Data
SELECT MAX (Sales_Data.Amount) AS agg FROM Sales_Data WHERE
Sales_Data.Amount <
3000
SELECT MIN (Sales_Data.Amount) AS agg FROM Sales_Data WHERE
Sales_Data.Amount <
3000
SQL expressions
Use expressions in WHERE, HAVING, and ORDER BY clauses of SELECT statements to form detailed
and sophisticated database queries.Valid expression elements are:
Field names
The most common expression is a simple field name, such as calc or Sales_Data.Invoice_ID.
Constants and literals
Constants are values that do not change. For example, in the expression PRICE * 1.05, the value 1.05 is a
constant. Or you might assign a value of 30 to the constant Number_Of_Days_In_June.
A literal is another kind of constant; but instead of having an assigned value, the literal itself is the value,
such as 'Paris' or '14:35:10'. A literal is a “what you see is what you get” constant.
You must enclose character constants (such as literals) in pairs of single quotation marks ('). To include a
single quotation mark in a character constant enclosed by single quotation marks, use two single quotation
marks together (for example, 'Don''t').
Aggregate function Returns
SUM The total of the values in a numeric field expression. For example, SUM(SALARY)
returns the sum of all salary field values.
AVG The average of the values in a numeric field expression. For example, AVG(SALARY)
returns the average of all salary field values.
COUNT The number of values in any field expression. For example, COUNT(NAME) returns the
number of name values. When using COUNT with a field name, COUNT returns the
number of non-null field values. A special example is COUNT(*), which returns the
number of records in the set, including records with null values.
MAX The maximum value in any field expression. For example, MAX(SALARY) returns the
maximum salary field value.
MIN The minimum value in any field expression. For example, MIN(SALARY) returns the
minimum salary field value.
Field names Numeric operators Relational operators
Constants and literals Character operators Logical operators
Exponential notation Date operators Functions