Add up and count

Last chapter you learned to take rows out of a table. That is looking at data one record at a time. But when you look at data at work, what you want to know is usually not about one record.

"What did we take this month?" "How many members are there?" "Which product is selling best?" Every one of those is a question that folds a great many rows into a single number. This chapter is about that.

The first thing to pick up is the tools for aggregating. Count the records, total them, average them, take the largest and the smallest. Those five will answer most questions. With a million rows you write exactly the same thing, and never having to think about the row count is one of SQL's strengths.

Where it gets a notch more interesting is grouping. Rather than one total for everything, you want "the total per category" or "the count per month". For that you say "bundle together the ones with the same value in this column, and aggregate each bundle". Then you get one result row per bundle.

Grouping differs from everything before it in that the shape of the table changes. Until now you were only reducing the rows or picking the columns of the original table. Group, and rows that were never in the original table get made — a summary row saying "category A comes to 12,000". Once that feeling clicks, the writing of aggregation makes sense all at once.

There is one more thing in this chapter that catches people out. It is that narrowing happens in two stages. "Narrow the rows before aggregating" and "narrow the aggregated result" are separate operations, written in different places with different words.

• before aggregating — "only this year's data"
• after aggregating — "show only the categories whose total is over 10,000"

Mix those two up and it either will not run or the answer will be wrong. When you get confused, ask yourself "is this about before the aggregating, or after?".

Something else worth watching is how rows with no value behave. When you count, whether you count "the rows" or "the rows that have a value" changes the answer. When you average, the empty rows drop out of the sum. They are not treated as zero, so if an average looks higher than you expected, this is the first place to look.

Put grouping and sorting together and it gets more useful still. Write "take the sales per category and sort them with the biggest first" and what sells lines up from the top. Keep only the first few and you have something you can put straight into a report.

The idea of bundling the same values pays off beyond aggregation. When you want to know "what values does this column hold?", grouping gives you a list with no repeats. Handed data you do not know, you look at the whole thing first, then use this to check what kinds of value are inside. That is nearly always the first move in real work.

Learn to give the columns you produce a readable name as well. Left alone, the expression itself becomes the column heading and it reads badly. Give it an alias and it can say "total amount" instead. That matters when you are making a result for other people to look at.

There is a knack to the order you write in, too. Do not jump straight to the aggregation — get a result without aggregating first. Check with your own eyes that the rows are the ones you meant, then lay the aggregation over the top. Do that and when an answer looks odd you can tell whether the narrowing or the aggregating is at fault.

Aggregation is the bridge from having data to being able to decide with it. A trend you could never see by staring at rows stands up as a number once you aggregate. This may be the most enjoyable part of SQL.