Tips for Creating Crosstabs

Use the CASE expression to return a sum expression when a column should include that information. If all you want is a count of the number of orders placed for these titles, the query from the last example would be this:

select      s.title_id,
               sum(case datepart(yy, s.ord_date)
              when 1992 then 1
              else 0 end
              ) “1992”,
              sum(case datepart(yy, s.ord_date)
            when 1993 then 1
            else 0 end
             ) “1993”,
              sum(case datepart(yy, s.ord_date)
            when 1994 then 1
            else 0 end
             ) “1994”
from      sales s, publishers p, titles t
where      s.title_id = t.title_id
          and t.pub_id = p.pub_id
         and p.pub_name = “New Moon Books”
group  by   s.title_id

title_id 1992        1993        1994
--------     -------     -------    ---------
BU2075      0         1          0
PS2091     0          0          4
PS2106     0          1          0
PS3333     0          1          0
PS7777     0          1          0

You still ask for the sum, not a count(). In this case, whenever there is a row in the sales table for a specific year, one is added to the sum for that year’s column.

Summary

Today you learned all about subqueries. You saw how to use subqueries that return a single result, subqueries that return lists, and subqueries that do the work of an inner join. You saw how to use subqueries to return aggregate information, which could then be applied in a normal (non-GROUP BY) query.

Lastly, you explored the CASE expression. You used the CASE expression to create a crosstab report. Keep the CASE expression in mind if you find yourself asking, “How can I do that with SQL?” It solves lots of interesting problems.

Q&A

Q Can I use more than one subquery, unnested, in a single statement?

A Yes. You may use as many subqueries as you like, as long as the nesting level does not exceed 16. For example, you could use this single query to retrieve the highest-priced and lowest-priced book in the titles table:

select      title, price
from        titles
where         price = (select max(price) from titles)
                 or price = (select min(price) from titles)
order  by  price
title                                  price
-------------------------------                --------------------------
You Can Combat Computer Stress!        2.99
The Gourmet Microwave                  2.99
But Is It User Friendly?               22.95
(3 row(s) affected)

Q Can I mix the subquery and join syntax together in the same query?

A Sure. Some users find that doing this helps them conceptualize how the server is creating their result set. Remember the limitations that the subqueries place on you. The 16-table limit still applies.

Q What if a subquery returns null, or if it returns no rows?

A If a subquery returns null, the outer query will simply compare to a null value. This won’t cause an error, but it may not be the solution you want. You can perform a subquery inside an ISNULL() function, too. So, if you want to avoid comparison to nulls, you could use a query like this, which finds books that cost more than the average UNDECIDED book. Because UNDECIDED books don’t have prices, the average would be null, and because no price is greater than null (null is an unknown value), the query would return 0 rows without the isnull. When a null price is returned for the UNDECIDED type, this query plugs in the average price for all books:

select         title
from           titles
where          price  >  isnull(
            (
               select       avg(price)
             from          titles
            where       type = ‘UNDECIDED’
            ),
            (
                  select avg(price) from titles
            ))

The first subquery gets a value for the average UNDECIDED book. If that is null (which it is), it asks for the average price of any book (the second argument in isnull(). The results of this query happen to be the same as if you asked for all books with prices greater than the average price of all books. It just takes a little longer to get there.

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 tables can appear in a single query?

2.  In which SQL clauses may a subquery appear?

Exercises

1.  List the types of books whose average prices are greater than the average price for all books. Include the average prices in the result set and sort on that column.

2.  List authors who have at least one book priced above average.

3.  Using a subquery, list the sales information for all books with the word “Computer” in the title.