IB Computer Science Topic A3.3.4: Constructing Calculations Within a Database Using Sql’s Aggregate Functions Notes

An Aggregate Function Turns Many Rows Into a Single Value

1. An aggregate function reads down a column across many rows and returns one number.
2. A query returning 4,000 purchases becomes a single total, which is what a report or a dashboard needs.
3. Used with GROUP BY the function returns one number per group instead of one for the whole table.

The Five Aggregate Functions and What Each One Returns

1. AVERAGE, written AVG
   1. Returns the mean of a numeric column.
   2. SELECT AVG(Quantity) AS AverageQuantity FROM Purchase;
2. COUNT
   1. Returns how many rows there are, rather than adding their values up.
   2. SELECT COUNT(ItemID) AS TotalItems FROM Purchase;
3. MAX
   1. Returns the highest value in a column, and works on dates and text as well as numbers.
4. MIN
   1. Returns the lowest value, and is often paired with MAX in one query.
   2. SELECT MAX(ItemCost) AS HighestCost, MIN(ItemCost) AS LowestCost FROM Item;
5. SUM
   1. Adds a numeric column up, and is meaningless on text.
   2. SELECT SUM(ItemCost) AS TotalCost FROM Item WHERE ArtistID = 105;

COUNT Has Three Forms and They Count Different Things

1. COUNT(*) counts every row that reached it, whatever those rows contain.
2. COUNT(ColumnName) counts only the rows where that column is not NULL.
3. COUNT(DISTINCT ColumnName) counts how many different values appear, so ten purchases of the same item count once.
4. On a Purchase table of 500 rows where 40 have no ItemID recorded and 90 different items were bought, the three forms return 500, 460 and 90.

Aggregates Skip NULL, Which Quietly Changes the Average

1. Every aggregate function except COUNT(*) ignores NULL values instead of treating them as zero.
2. Suppose five items cost 100, 200, 300, 400 and NULL because the fifth price was never entered.
3. SUM(ItemCost) returns 1000, and AVG(ItemCost) returns 250 because it divides by four, not by five.
4. Had the missing price been stored as 0 the average would be 200, so how missing data is recorded changes the reported figure.

Aggregating per Group Rather Than per Table

1. With no GROUP BY, an aggregate collapses the whole result to one row.
2. Adding GROUP BY ArtistID runs the same calculation once per artist and returns one row each.
3. Every column in the SELECT list must either be aggregated or named in the GROUP BY clause.
4. HAVING then filters those groups, because WHERE has already run before the grouping took place.

What Aggregated Figures Are Actually Used For

1. Sales reporting
   1. SUM gives revenue per month, AVG gives the average order value, and COUNT(DISTINCT CustomerID) gives how many people bought anything.
2. Inventory management
   1. MIN flags the stock lines closest to running out and SUM values the warehouse.
3. Popularity ranking
   1. COUNT per item with a descending sort produces a best-seller list in one query.
   2. SELECT ItemID, COUNT(*) AS PurchaseCount FROM Purchase GROUP BY ItemID ORDER BY PurchaseCount DESC;

Choosing the Function the Question Is Asking For

1. How many is COUNT, how much in total is SUM, and typically is AVG.
2. Most expensive or earliest is MAX or MIN, and per each in the wording signals a GROUP BY.
3. Only those with more than is a condition on the group, so it belongs in HAVING.