Math · Note 3
Sigma, and what it is really doing
The summation sign has exactly four parts, and every sigma you will ever meet has the same four. Covers summing over a range, summing over a set — the form that matters for data — and summing with a condition, which is the WHERE clause.
is a capital Greek sigma, and it means add up.
Say it: the sum, from i equals 1 to 4, of i.
It has exactly four parts, and every sigma you ever meet has these four: the index, the variable that changes, usually , , or ; where it starts; where it stops, which is inclusive — it does include 4; and the body, which is what to add up, written in terms of the index.
Mechanically: take the body, substitute , then , and so on to the stop value, and add the results. So
The body does not have to mention the index:
Read that again, because it is the single most useful fact in the series: summing 1 counts things.
And the empty sum is 0. If the stop is before the start there is nothing to add, and the answer is zero, not undefined: .
Summing over a set
This is the form that matters for data, and it is a small change. Instead of counting the index from a number to a number, you let it range over the elements of a set.
Say it: the sum, over every x in S, of x. With that is .
Why this form is the important one: a table has no natural numbering. Rows are not numbered 1 to n; they are just there. Writing the sum over says "do this once for each element", which is exactly what you want, and it does not require you to invent an order that does not exist.
Two consequences worth memorizing:
Counting is summing ones, and the sum over an empty set is zero.
Now the first real one. Total revenue across the whole database:
Work it through with the four lines from note one: .
Summing with a condition
Put a condition under the sigma and only the matching elements are added:
Say it: the sum over x in S with x greater than 3, of x. With that is . You will also see it written on one line, . Same thing.
This is `WHERE`. And it is interchangeable with set-builder — these two are identical:
The first reads better, so prefer it. But knowing they are the same is what lets you move a filter around inside a bigger formula without breaking it — which is exactly what a query planner does, and for the same reason.
Exercises
Compute and .
Answer
and .
Compute .
Answer
. Summing a constant 1 counts the terms.
Compute .
Answer
. Start and stop are the same, so there is exactly one term.
Write "add up the first ten square numbers" in sigma notation.
Answer
.
Compute .
Answer
.
Compute . What else is this number called?
Answer
, which is .
Compute .
Answer
. That is the value of order 10.
Compute .
Answer
— summing 1 over a filtered set counts it. This is `COUNT(*) ... WHERE city = 'Leeds'`.
Write, in notation, "how many lines have a unit price above 10".
Answer
, or equivalently . The answer is .
Questions
What are the four parts of a sigma?
The index variable that changes, the value it starts at, the value it stops at, and the body that says what to add. The stop value is included. Substituting each index value into the body and adding the results is the entire operation.
What does summing over a set mean?
It means adding the body once for each element of the set, in no particular order. It is used instead of a numbered range whenever the collection has no natural ordering, which is the normal case for rows in a database table.
What is an empty sum?
A sum with no terms, which equals zero. It arises whenever the set being summed over is empty, and getting it right is why a well-written formula returns zero revenue for a customer with no orders rather than an error.