Summarizing like a pivot table: GROUP BY with COUNT, SUM and AVG

Your manager wants totals by category. It's Monday morning, and your manager asks a simple question. How much did each product category sell, and how many orders did each one get? In Excel, you'd reach for a pivot table. You'd drag Category into Rows and drag Amount into Values. Then you'd drag Amount into Values a second time and set that copy to Count. That works well, until the data has half a million rows, or you have to rebuild the same pivot every week. In this lesson you'll learn the SQL version of a pivot table. It's a short query you write once, save, and rerun whenever the numbers change. It works on our online shop's orders table in SQLite.

What you will learn. Here's the plan for the next few minutes. First, you'll learn three summary functions, COUNT, SUM and AVG, and use them on the whole orders table. Second, you'll learn GROUP BY, which splits the rows into groups, exactly like the Rows area of a pivot table does. Third, you'll learn how to keep only some of those groups with a clause called HAVING, and how to sort the finished summary so the biggest numbers come first. By the end, you'll be able to answer the Monday morning question in about six lines of SQL.

How a pivot table really works. Before any SQL, let's look at what a pivot table actually does behind the scenes, because SQL does exactly the same four steps. It starts with all the rows of your data. Then it splits those rows into piles, one pile for each distinct value in the column you picked, say one pile for Books, one for Toys, one for Games. Next, it calculates a number for each pile, like a sum or a count. Finally, it shows you one row per pile. Keep this picture in mind: split, then calculate, then one row per group. That's all GROUP BY is.

Summary functions on the whole table. Let's start without any groups. COUNT, SUM and AVG are called aggregate functions. Aggregate just means they squeeze many rows into one number, like the total at the bottom of an Excel column. COUNT star counts rows, the star meaning count every row. SUM adds up a column, and AVG works out the average. Our small orders table has eight orders. Run this query and you get one single row back: eight orders, two hundred thirty two in total sales, and an average order of twenty nine. That's the same as typing COUNTA, SUM and AVERAGE formulas under your column, but in one line.

GROUP BY: totals per category. Now let's add groups. Here are our eight orders, each with a customer, a category and an amount. Watch our summary query. It picks the category column, then counts, sums and averages the amount. AS just gives each result column a name, like a column header. ROUND trims the average to two decimal places. The new line at the bottom, GROUP BY category, is the pivot table's Rows area. The rows fall into three piles: Books, Toys and Games. Then each function runs once per pile. The finished summary appears with one row per category. Books has three orders totalling seventy. Toys has three orders totalling seventy two. Games has just two orders, but they total ninety, with an average of forty five.

The one rule of GROUP BY. GROUP BY has one rule you should never break. Every column you list after SELECT must be either in the GROUP BY list, or wrapped inside a summary function. Think about the pivot table again. Every field you use sits either in the Rows area or in the Values area. Category was in GROUP BY, so it's fine to show it. Amount was inside SUM and AVG, so that's fine too. But what about customer? A category like Books has three different customers, so there's no single customer name to show on the Books row. Some databases refuse such a query outright. SQLite runs it anyway, as you'll see in the common mistake later, so the rule is yours to keep.

Run it yourself in Python. Here's a nice shortcut: Python comes with SQLite built in, so you can try every query from this lesson without installing anything. This small program creates the orders table in memory, fills in our eight orders, and runs the category summary. You met ROUND already: it trims the average to two decimal places, just like the Decrease Decimal button in Excel. The new addition is ORDER BY SUM of amount, descending, which sorts the summary so the biggest category comes first. Games comes out on top with ninety, then Toys with seventy two, then Books with seventy.

HAVING: filter the groups. Now marketing wants to know about repeat customers. Let's group by customer this time. That gives us four customer piles: Ana, Ben, Chen and Dina. We only want customers with at least two orders. You can't use WHERE for that, because WHERE checks single rows before any groups exist. Instead we use HAVING, which checks each group after it's built. HAVING count star is at least two, so Dina drops out with her single order. Then ORDER BY total, descending, puts the biggest spender first. We end with three loyal customers: Ana with sixty five, Chen with sixty two, and Ben with forty five.

Pivot table fields in SQL. Let's put the two worlds side by side, so you can translate any pivot table you already know. The Rows area becomes GROUP BY. The Values area becomes your summary functions: COUNT, SUM, AVG, and also MIN and MAX for the smallest and largest value. The report filter at the top of a pivot, like showing only last month, becomes WHERE. A value filter, like showing only customers above a certain total, becomes HAVING. Sorting becomes ORDER BY. Want two row fields, like customer and then category? Just list both columns in GROUP BY, separated by a comma.

Common mistake: the silent extra column. Here's the trap that catches almost every beginner. This query adds category to the SELECT list, but groups only by customer. Many databases would refuse to run it. SQLite doesn't. It quietly runs and shows a category for each customer, taken from whichever of that customer's rows it happened to look at. Ana bought Books, Toys and Games, yet her row shows just one of them, and it looks perfectly believable in a report. There are two fixes. Add category to GROUP BY, so you get one row per customer and category. Or remove category from SELECT.

Recap. Let's recap. First, summary functions like COUNT, SUM and AVG squeeze many rows into a single number, just like totals under an Excel column. Second, GROUP BY splits the rows into groups and gives you one row per group, exactly like the Rows area of a pivot table, and every column you select must be grouped or summarized. Third, WHERE filters individual rows before grouping, while HAVING filters whole groups after grouping, and ORDER BY sorts the finished summary. With these ideas, you can already rebuild most of the pivot tables you make each week.

Your exercise. Now it's your turn. Take the Python program from this lesson and change only the query. Group the orders by customer. For each customer, show the number of orders, their largest order using MAX, and their average order rounded to two decimals. Then keep only customers whose average order is above twenty five, and sort the result by that average, highest first. Hint: the average filter needs HAVING, not WHERE. Check your answer by hand against the eight orders. In the next lesson, we'll combine tables, the SQL way of doing a VLOOKUP.