SQL Querying for Data Analysis
A question about tabular sales data is answered by querying the table, not by generating a number from general knowledge.
Why a Sales Question Needs the Table
Suppose a user asks a system a plain-language question: how many units of Fruity Fedora were sold in the last 7 days? The answer is not written down anywhere as a ready-made sentence. No general knowledge contains it either. It has to be calculated from the sales records that the business has stored.
The approach is direct. The system queries the relevant table for all orders that involve Fruity Fedora, and then sums the number of units across those orders. The number the user receives is therefore determined by what the table contains and by how the query selects and combines its rows. The system does not produce the figure from memory or from a guess.
This is why the rest of this article treats the query as the mechanism that connects the question to the data. If you can read the query, you can predict what it will return and find out why it returned something unexpected.
Before reading any query, it helps to state what a correct answer must do. It has to ignore orders for other products, ignore orders that fall outside the 7-day window, and then add up only the units that remain. Each of those three requirements maps to a specific part of the query.
Reading the Query Clause by Clause
Read a query as a list of decisions the database makes: which column to add up, which table to read, which rows to keep, and which rows to throw away. The example below is an illustrative query written for this lesson. Its table name and column names (sales, product, order_date, units) are stand-ins chosen to match the question, not a schema quoted from the source.
The SELECT line names what is produced: the SUM of the units column. The FROM line names the table the database reads. The WHERE line decides which rows take part at all. The product condition removes orders for other products, and the date condition defines the time window. Only the rows that pass the WHERE line are passed to SUM.
From Matching Rows to One Total
SUM is an aggregation function. It takes a set of values and returns one value that represents their total. Here the values are the units entries in the rows that survived the WHERE filter. The database does not return those rows to the user. It collapses them into a single figure, and that figure is the answer to the original question.
In this expression, M is the set of rows that pass the WHERE filter, and units_i is the units value in row i. The sum runs only over M. Rows outside M contribute nothing, no matter how large their units values are.
Tracing a Week of Fruity Fedora Sales
The following small table is illustrative and was written for this lesson. It shows five rows from a sales table. Trace the query against it, step by step, before reading the explanation.
| Row | product | order_date | units | Passes WHERE? |
|---|---|---|---|---|
| A | Fruity Fedora | 2024-03-02 | 4 | Yes |
| B | Mango Mint | 2024-03-03 | 9 | No: other product |
| C | Fruity Fedora | 2024-03-05 | 7 | Yes |
| D | Fruity Fedora | 2024-02-10 | 5 | No: outside window |
| E | Fruity Fedora | 2024-03-07 | 2 | Yes |
Illustrative sales rows, checked against the product and date conditions
Computing the Weekly Total
Using the five illustrative rows, what does SELECT SUM(units) ... WHERE product = 'Fruity Fedora' AND order_date BETWEEN '2024-03-02' AND '2024-03-08' return?
- 1Read the table
The FROM clause makes all five rows available to the query.
- 2Apply the product condition
Row B is removed because its product is Mango Mint. Rows A, C, D, and E remain.
- 3Apply the date condition
Row D is removed because 2024-02-10 falls outside the window. Rows A, C, and E remain.
- 4Collect the units
The surviving units values are 4, 7, and 2.
- 5Apply SUM
4 + 7 + 2 = 13. The database returns one value: 13.
The query returns 13. Notice that the larger values 9 and 5 never reach the addition, because the filters removed their rows first.
What do you think happens?
Before reading the answer, predict: if the date condition were removed, which rows would be added, and what would the total become?
Reveal answer
18, because row D (5 units) would be added to the 13
Without the date condition, the Fruity Fedora rows are A (4), C (7), D (5), and E (2). Their sum is 4 + 7 + 5 + 2 = 18. Row B is still excluded by the product condition, so its 9 units never enter the total.
Where a Wrong Answer Starts
The order of operations is the key to debugging. The filter comes before the sum. If the filter is wrong, the sum is computed over the wrong set of rows, and the arithmetic can be perfectly correct while the answer is still wrong. A wrong total does not always mean that the addition failed.
When a result diverges from what you expect, locate the responsible clause before assuming the arithmetic is wrong. Check the filter, then the window, then the aggregate, in that order. An error introduced at any earlier stage passes forward into every later stage, so the first stage that does not match your expectation is usually the origin.
Common mistake Assuming that a wrong total means the SUM is wrong
What it looks likeA total of 13 is reported where 18 was expected, and the reader starts checking the addition.
Why it failsThe addition may be correct. The WHERE clause may have kept or dropped the wrong rows before SUM ever ran.
Do this insteadCheck the product condition and the date window first, then confirm which rows reached the aggregate.
Common mistake Forgetting that the date condition defines the time window
What it looks likeA query for the last 7 days omits the date condition, so it adds every Fruity Fedora order ever recorded.
Why it failsThe question asks for a limited window, and the query must restrict rows to that window.
Do this insteadConfirm that the date condition matches the start and end dates the question specifies.
Common mistake Reading the query as if SUM returned the rows
What it looks likeExpecting a list of Fruity Fedora orders from a query that uses SUM.
Why it failsSUM collapses the matching rows into one value. The database does not return the individual rows to the user.
Do this insteadRead the aggregate as producing a single figure, and use a different query if you need the individual rows.
Common mistake A product name typed differently from the stored value silently drops matching rows.
Practice Tracing a Total
Using the five illustrative rows from the worked example, a colleague runs the same query but with the date window changed to 2024-03-05 through 2024-03-08. Which rows survive, and what total is returned? Trace the product condition first, then the date condition, then the SUM.
Hints
- Start with the rows that match Fruity Fedora. Rows B is excluded by the product condition.
- Check each remaining row's
order_dateagainst 2024-03-05 through 2024-03-08. - Add only the units of rows that survive both conditions.
Key Takeaways
- A question about tabular sales data is answered by querying the table, because the number is calculated from stored records rather than produced from general knowledge.
- In a SELECT ... FROM ... WHERE ... SUM query, the SELECT line names the aggregate, the FROM line names the table, and the WHERE line decides which rows take part.
- SUM collapses the rows that survive the filter into one value, so the filter determines the answer as much as the addition does.
- When a total is wrong, check the product condition, then the date window, then the aggregate, because an error at an earlier stage carries into every later stage.