5.2. Row-Level Functions
Whereas aggregate functions operate on multiple rows for a result, row-level
functions
operate on values in single rows, one row at a time. In this section, we look at row-level functions
that are used in calculationsfor example, row-level functions that are used to add a number to a
column, the ROUND function, the ISNULL function, and others.
5.2.1. Arithmetic Operations on a Column
A row-level "function" can be used to perform an arithmetic operation on a column.
 |
Strictly speaking a row-level "function" is not a function,
but an operation performed in a result set. But the use of arithmetic
operations in result sets behaves like functions.
|
|
For example, in the Employee table, if we wanted to display every person's
wage plus 5, we could type the following:
SELECT wage, (wage + 5) AS [wage + 5]
FROM Employee
In this query, from the Employee table, first the wage is displayed, then
the wage is incremented by five with (wage + 5), and displayed.
This query produces the following output:
wage wage + 5
------------ ------------
10.0000 15.0000
15.0000 20.0000
18.0000 23.0000
NULL NULL
20.0000 25.0000
(5 row(s) affected)
|
Similarly, values can be subtracted (with the -
operator), multiplied (with the * operator), and divided (with the
/ operator) to and from columns.
|
|
Once again, note that (wage + 5) is only a "read-only" or "display-only"
function, because we are using it in a SELECT statement. The wage in the Employee
table is not actually changing. We are only displaying what the wage + 5 is. To actually increase
the wage in the Employee table by 5, we would have to use the UPDATE command. Any
other arithmetic operation may be performed on numeric data.
5.2.2. The ROUND Function
The ROUND function rounds numbers to a specified number of decimal places.
For example, in the Employee table, if you wanted to divide every person's wage by
3 (a third of the wage), you would type (wage/3). Then, to round this, you could
use ROUND(wage/3), and include the precision (number of decimal places) after the comma. In
query form, this would be:
SELECT names, wage, ROUND((wage/3), 2) AS [wage/3]
FROM Employee
This query produces the following output:
names wage wage/3
-------------------- --------------------- ---------------------
Sumon Bagui 10.00 3.33
Sudip Bagui 15.00 5.00
Priyashi Saha 18.00 6.00
Ed Evans NULL NULL
Genny George 20.00 6.67
(5 row(s) affected)
In this example, the values of (wage/3) are rounded up to two decimal places
because of the "2" after the comma after ROUND(wage/3).
5.2.3. Other Common Numeric Functions
Other very common numeric
functions include:
-
CEILING(attribute), which returns the next larger integer value when
a number contains decimal places.
-
FLOOR(attribute), which returns the next lower integer value when a
number contains decimal places.
-
SQRT(attribute), which returns the square root of positive numeric
values.
-
ABS(attribute), which returns the absolute value of any numeric
value.
-
SQUARE(attribute), which returns a number squared.
5.2.4. The ISNULL Function
The results of the queries in the preceding sections show not only that nulls are
ignored, but that if a null is contained in a calculation on a row, the result is always null. We
will illustrate, with a couple of examples, how to handle this NULL issue.
5.2.4.1. Example 1
In the first example, we will illustrate how to handle the NULL problem and
also illustrate how to create variables on the fly. SQL Server allows you to create variables on the
fly using a DECLARE statement followed by a @, the variable name (a or
b, in our example) and then data type of the variable (both declared as FLOAT in
our example). Variables are assigned values using the SET statement. And variables can be
added in the SELECT statement.
|
A variable is a special place in memory used to hold data
temporarily.
|
|
So, type the following sequence to declare the variables (a and b),
assign values to them, and then add them together:
DECLARE @a FLOAT, @b FLOAT
SET @a = 3
SET @b = 2
SELECT @a + @b AS 'A + B = '
This query gives the result:
A + B =
----------------
5
(1 row(s) affected)
SQL Server allows the use of SELECT with no FROM clause for such
calculations as we have illustrated.
Now, if you set the variable a to null, as follows:
DECLARE @a FLOAT, @b FLOAT
SET @a = NULL
SET @b = 2
SELECT @a + @b AS 'A + B = '
You get this:
A + B =
----------------
NULL
(1 row(s) affected)
To handle the null issue, SQL Server provides a row-level function, ISNULL,
which returns a value if a table value is null. The ISNULL function has the following form:
ISNULL(expression1, ValueIfNull)
The ISNULL function says that if the expression (or column value) is not null, return the value, but if the value is null, return ValueIfNull. Note that the
ValueIfNull must be compatible with the data type. For example, if you wanted to use a
default value of zero for a null in the previous example, you could type this:
DECLARE @a FLOAT, @b FLOAT
SET @a = NULL
SET @b = 2
SELECT ISNULL(@a, 0) + ISNULL(@b, 0) AS 'A + B = '
Which would give:
A + B =
-----------------
2
(1 row(s) affected)
Here, @b is unaffected, but @a is set to zero for the result set as
a result of the ISNULL function. @a is not actually changed, it is replaced for
the purposes of the query.
5.2.4.2. Example 2
For the second example we will use the Employee table. To multiply the
wage by hours and avoid the null-result problem by making the nulls act like
zeros, a query could read:
SELECT names, wage, hours, ISNULL(wage, 0)*ISNULL(hours,0) AS [wage*hours]
FROM Employee
This query would produce the following output:
names wage hours wage*hours
--------------- ------------ ----------- ------------
Sumon Bagui 10.00 40 400.00
Sudip Bagui 15.00 30 450.00
Priyashi Saha 18.00 NULL 0.00
Ed Evans NULL 10 0.00
Genny George 20.00 40 800.00
(5 row(s) affected)
ISNULL does not have to have a ValueIfNull equal to zero. For
example, if you want to assume that the number of hours is 40 if the value for
hours is null, then you could use the following expression:
SELECT names, wage, new_wage = ISNULL(wage, 40)
FROM Employee
This query would give:
names wage new_wage
--------------- ------------ ------------
Sumon Bagui 10.00 10.00
Sudip Bagui 15.00 15.00
Priyashi Saha 18.00 18.00
Ed Evans NULL 40.00
Genny George 20.00 20.00
(5 row(s) affected)
5.2.5. The NULLIF Function
SQL Server also has a NULLIF function, which returns a NULL if
expression1 = expression2. If the expressions are not
equal, then expression1 is returned. The NULLIF function has the following
form:
NULLIF(expression1, expression2)
For example, if we want to see whether the wage is 0, we would type:
SELECT names, wage, new_wage = NULLIF(wage, 0)
FROM Employee
This query would give:
names wage new_wage
--------------- ------------ ------------
Sumon Bagui 10.00 10.00
Sudip Bagui 15.00 15.00
Priyashi Saha 18.00 18.00
Ed Evans NULL NULL
Genny George 20.00 20.00
(5 row(s) affected)
From these results we can see that because none of the wages are equal to 0, the wage
(expression1) is returned in every case. Even the NULL wage (Ed Evans's
wage) is not equal to 0, but NULL is returned anyway, as the value in question is
NULL.
If, for example, a wage 15 was unacceptable for some reason, you could null out the
value of 15 using the NULLIF function like this:
SELECT names, wage,
new_wage = NULLIF(wage, 15)
FROM Employee
This query would give:
names wage new_wage
--------------- ------------ ------------
Sumon Bagui 10.00 10.00
Sudip Bagui 15.00 NULL
Priyashi Saha 18.00 18.00
Ed Evans NULL NULL
Genny George 20.00 20.00
(5 row(s) affected)
Again, as can be noted from the previous set of results, you have to be very careful
about the interpretation of the output obtained from a NULLIF function if there were
already nulls present in the columns being tested. Ed Evans's wage was not equal to15, but had a
NULL originally (and this may be wrongly interpreted when the NULLIF function is
being used).
5.2.6. Other Row-Level Functions
Other row-level functions in SQL Server include ABS, which returns the
absolute value of a numeric expression. For example, if we wanted to find the absolute value of
-999.99, we could type the following:
SELECT ABS(-999.99) AS [Absolute Value]
This query would produce the following output:
Absolute Value
--------------
999.99
(1 row(s) affected)
There are also several other row-level trigonometric functions available in Server
SQL 2005, including SIN, COS, TAN, LOG, and so forth. But, as
these functions are less commonly used, we will not discuss them.
|
