12 Grouping data

 

If you’re accustomed to working with spreadsheets, rather than relational data in a database, the past three chapters may have been a bit challenging for you. After all, in spreadsheets you often work with a single set of data instead of multiple sets. If the concepts in those chapters are new to you, take heart; this chapter covers concepts that should be very familiar to most spreadsheet users.

One useful aspect of spreadsheets is that they allow us to do mathematical calculations on a range of data quickly. If we want to find the total of all values in a column, for example, we can click the AutoSum button, which places the desired sum amount in a particular cell. If we highlight that cell, we see that the spreadsheet used the word SUM with the defined range of cells. SUM represents a function, which is a command that performs a predefined calculation.

Although we have no button in the SQL language to calculate totals automatically, we do have functions like SUM to help us perform mathematical calculations. Moreover, in a relational database, we have much more flexibility in the way we perform these calculations than we have in spreadsheets.

12.1 Aggregate functions

Throughout the rest of this book, we’ll be discussing different kinds of functions in SQL. A function is a keyword that makes it easy to perform calculations or other actions. SQL has many functions for calculating all sorts of values for converting dates, formatting data, and doing much more.

12.1.1 The SUM function

12.1.2 The COUNT function

12.1.3 The MIN function

12.1.4 The MAX function

12.1.5 The AVG function

12.1.6 Filtering and aggregating combined values

12.2 Aggregating data with GROUP BY

12.2.1 GROUP BY requirements

12.2.2 GROUP BY and null values

12.3 Filtering with HAVING

12.4 Logical query processing

12.5 The DISTINCT keyword

12.6 Lab

12.7 Lab answers