12 Aggregating data

This chapter eases spreadsheet users into SQL by showing how familiar “spreadsheet math” translates to database queries. It introduces SQL functions that summarize data across rows—especially the aggregate functions SUM, COUNT, MIN, MAX, and AVG. You learn core habits such as putting target columns or expressions in parentheses, using aliases to give meaningful names to computed outputs, and understanding how aggregates handle nulls (they generally ignore them). While SUM and AVG are for numeric data, MIN and MAX can also work with dates and text, returning the earliest/latest values based on sort rules; AVG may return precise decimals that you can format later.

Beyond whole-table totals, the chapter shows how to analyze data by subgroup using GROUP BY. You can compute expressions inside aggregates (for example, SUM(Quantity * ItemPrice)), then group by keys like OrderID to get one result per group. It stresses the rule that every selected column must either be aggregated or appear in the GROUP BY list, and it clarifies clause placement: WHERE filters rows before grouping, GROUP BY forms the groups, and ORDER BY sorts the final results. GROUP BY includes nulls as their own group, so you can account for “no value” cases. To filter aggregated results (such as “only promotion codes used at least three times”), you use HAVING, not WHERE; some systems allow aggregate aliases in HAVING, but portability cautions apply.

The chapter also explains logical query processing—the order in which the database evaluates clauses (FROM, WHERE, SELECT, GROUP BY, HAVING, ORDER BY)—and how that affects when table and column aliases are usable. Finally, it demystifies DISTINCT: it removes duplicate rows but effectively performs a grouping step, which can add work on large datasets; prefer GROUP BY when you need explicit aggregates, and don’t use DISTINCT to hide duplicates caused by incorrect joins. Hands-on exercises reinforce these concepts by exploring data-type compatibility with functions, diagnosing why aliases can’t appear in WHERE, counting rows, finding date ranges, and combining subqueries with GROUP BY to compute per-order metrics.

The quantity of all orders is shown in a column with no alias. The default column name is the calculation.

There are 50 rows in the orderheader table with a value for OrderID, which is all the rows in the table.

There are only 20 rows in the orderheader table that have a value for PromotionID.

The minimum price of any item in the orderitem table is $4.95.

The maximum price of any item in the orderitem table is $12.95.

The average price of all titles in the title table

The total value of all orders in the orderitem table is $573.50.

These are 8 of the 50 rows returned showing the Order Total for all orders.

These are the count of all PromotionID values in the orderheader table. This includes null values since GROUP BY does not exclude nulls.

The only promotion codes that have been used at least three times

The distinct title names that have been included in orders in the orderitem table

1. You may have missed it earlier in the book, but it was noted how date and time values are actually stored as numeric values that your RDBMS can interpret as dates and times. Although it’s practically useless to us, the value you see here is the RDBMS trying to make sense of using a SUM of date values.
2. The query will fail because a column alias is used in the WHERE clause, and the logical query processing order is to evaluate the WHERE clause before the SELECT clause. Because of this, the RDBMS doesn’t know what “PromoCode” is when evaluating the WHERE clause, since the alias is determined later in the query in the SELECT clause.
3. To count the number of rows in a table, you can use COUNT(*)

4. To select the minimum and maximum publication dates, you can use the MIN and MAX functions:

5. There are a few different ways you can do this, but the first is to group the total value of all orders as we did in section 12.2.1 and then select an average value of all those order totals, like this:

FAQ

SELECT SUM(Quantity) AS TotalQuantity
FROM orderitem;

-- Counts rows with a non-null PromotionID
SELECT COUNT(PromotionID) FROM orderheader;

-- Counts all rows in the table
SELECT COUNT(*) FROM orderheader;

SELECT ROUND(AVG(Price), 2) AS AveragePrice
FROM title;

SELECT OrderID, SUM(Quantity * ItemPrice) AS OrderTotal
FROM orderitem
GROUP BY OrderID
ORDER BY OrderID;

SELECT p.PromotionCode, COUNT(*) AS Uses
FROM orderheader oh
JOIN promotion p ON p.PromotionID = oh.PromotionID
GROUP BY p.PromotionCode
HAVING COUNT(*) >= 3;