The ISNULL() Function

The ISNULL() function permits a way to include null values in aggregate calculations. It takes two arguments. Here is the syntax for ISNULL():

isnull(<expression>,       <value>)

The first argument is the expression on which the calculation will be performed, usually a column. The second argument is the value that will replace a null. If the expression evaluates to null, the second parameter is returned by this function. If the expression resolves to anything else, the expression is returned.

The following query demonstrates the effect of isnull() on the titles table:

      select       title_id,  price,  isnull(price,  $35)
      from        titles
      order  by  price

      title_id  price                           Suggested Price
      --------        ---------------------           --------------
      MC3026    (null)                        35.00
      PC9999   (null)                         35.00
      BU2075   2.99                           2.99
      MC3021    2.99                          2.99
      PS2106   7.00                           7.00
      PS7777   7.99                           7.99
      PS2091   10.95                          10.95
      BU1111    11.95                         11.95
      TC4203    11.95                         11.95
      TC7777    14.99                         14.99
      BU1032    19.99                         19.99
      BU7832    19.99                         19.99
      MC2222    19.99                         19.99
      PS3333   19.99                          19.99
      PC8888   20.00                          20.00
      TC3218    20.95                         20.95
      PS1372   21.59                          21.59
      PC1035    22.95                         22.95
      (18   row(s)    affected)

There are two books in the titles table with null in their price column. When isnull() evaluates those columns, it returns $35 for the third column in the result set. For the other columns, it simply returns the value of the column.

You can use isnull inside an aggregate. If you want to know the average price of a book, you just ask for the AVG(price) on the titles table. But two books have been excluded from the average calculation. Suppose you know that these two books will be priced at either $25 or $30. You’re interested to know how this affects the average price of all of the books:

         select     avg( isnull( price,  $25 ) )  “Expensive”,
                   avg( isnull( price, $30 ) ) “Gouging”
         from       titles

         Expensive      Gouging
         --------------    --------------
         15.90          16.46

In the first column, both books with null prices are assumed to be $25. The average price is then calculated, and this time the null priced books can finally be a part of the calculation. The second column has a higher price average because the null priced books are assumed to be $30.

The ISNULL function doesn’t change the value of the row in the table; it only assumes a value for the purposes of a single query.

Summary

Today you learned a lot about aggregates. There are five aggregates: SUM, AVG, COUNT, MIN, and MAX. Null values are not included in aggregate calculations. The ISNULL function can be used to assume a value for null rows. Aggregates are calculated based on the expression inside their parentheses. Any expression can be used, from a simple column to complex calculations based on one or many columns.

Tomorrow, you will explore grouping with the GROUP BY clause and how that affects aggregates.

Q&A

Q Because the rowcount coming back from an aggregate is always one, is there any other way to determine on how many columns the aggregate acted?

A The best way is to perform a count(<column>) in addition to your other aggregates. This will return the number of columns that passed the WHERE test and are not null—the same set of columns upon which any other aggregates in the query will have acted.

Q If the server won’t convert my datatype to hold fractions, what happens when I get the sum of something too big to be held?

A Suppose that you have a table of tinyints. The max value for a tinyint is 255. If the sum is, say, 400, the server is smart enough to promote the datatype to an integer. It skips right over smallint. In short, you don’t have to worry about datatype conversion during sums.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  How many rows are returned by a simple aggregate?

2.  Briefly define what the five aggregate functions do.

3.  What happens to null values during the calculation of an aggregate?

4.  What function enables you to provide a value for nulls for the purposes of calculating an aggregate?

Exercises

1.  What was the largest advance that a book received? (Hint: Advances are stored in titles.advance.)

2.  Which book received that advance?

3.  When was the first employee hired?

4.  How many books were sold in 1993? (Hint: Sales information is stored in the sales table.)