Summary

Today, you learned a number of ways to produce totals and subtotals in your result sets. You learned how to calculate scalar aggregates yesterday and vector aggregates today. You learned what the CUBE and ROLLUP operators do inside a GROUP BY clause. You also learned how to cull rows from a final result set, using HAVING, without affecting the calculation of the aggregates that produced those rows.

Q&A

Q What can the WHERE clause do that the HAVING clause cannot?

A The WHERE clause restricts the rows that are used to evaluate a query. If a row can’t pass the conditions in the WHERE clause, it is not used in the calculation of an aggregate in the query.

Q What can the HAVING clause do that the WHERE cannot?

A Because HAVING operates on a result set, and not on the rows in tables, an aggregate may appear in a HAVING clause. An aggregate may never appear in a WHERE clause, although it may be part of a subquery that feeds the WHERE. Subqueries are explored on Day 8.

Q Where did CUBE and ROLLUP get their names?

A The CUBE operator calculates superaggregates on all possible combinations of groups and subgroups appearing in the GROUP BY clause. Mathematically, this assemblage of combinations of all possible subgroups forms an n-dimensional cube.

The ROLLUP operator calculates running totals (or maximums, minimums, and so on), which take the previous rows’ aggregates, and then “rolls them up” into a new aggregate on its own row.

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.  What does the GROUP BY clause do?

2.  What are COMPUTE and COMPUTE BY good for?

3.  On what does the HAVING clause operate?

Exercises

1.  Using the sales table, list each title that has sold, along with the total number of units sold. Label the total column, “Books Sold.”

2.  Now, sort the results by total books sold, with the best-selling title first.

3.  Find the grand total of books sold, first using the COMPUTE clause and then, in another query, by using the ROLLUP operator.

4.  Now, using the ROLLUP and COMPUTE queries, apply a HAVING clause to cull rows selling fewer than 50 total units.