Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

5. Aggregation

From its inception, the SQL Standard has supported aggregating data, which can be useful for generating reports. However, when you start aggregating something, it is no longer enough just to say, “I want data from this and that, and only if it’s x, y, or z.” To ask for totals over “this and that” is usually insufficient; typically, we want to see “totals per customer,” “count of orders by day,” or “average sales of each category by month.” It is the part after the “per,” “by,” and “of each” that requires additional attention. In this chapter we discuss GROUP BY and HAVING clauses that deal with those classes of questions. You will also learn about techniques to get the best performance from your aggregation and avoid common mistakes ...