SQL for Finance: Asking a Database a Precise Question

What is a query, and what does it actually ask?

A finance analyst does one thing with a stored table: asks it a question precise enough that a second person, months later, can run the same question and get the same figure. Running the same question twice and getting the same figure twice is harder than it sounds, and the difficulty is almost never in the arithmetic.

So. A query is a question, written down, in a fixed order. A query is nothing more than that. The analyst names what is to come back, names the table it comes from, names which of its rows are to be counted, and says whether the answer should come back as one figure or as one figure for each group. Four decisions, always in that sequence, and each of them narrows what the answer can be. Nothing about that is exotic. The clerk at the market office does exactly the same four things whenever a trader asks how the pitches are doing, except the clerk does them in her head and nobody can check her afterwards.

Here is the everyday version. Suppose a clerk at the Neelbagh market office wants the month totals pulled out of the ledger, and she shouts the request across the room to a colleague. The colleague hears something, does something, and hands back a number. If that number later looks odd, there is nothing to inspect. The request evaporated the moment it was spoken, so the only way to find out what was actually done is to ask again and hope the answer is the same. Now suppose instead she fills in the office requisition slip: pitches in the covered market, months one to four, totals only, exclude any month with no return filed. Same request. But now it is on paper, and a third person who was never in the room can pick up the slip, read exactly what was asked, disagree with the last line if she wants to, and rerun it.

The reason a written question beats a figure copied off a screen is not that it is more accurate; it is that it can be reread, argued with and run again by somebody who was not there when it was asked. A figure on its own carries none of that. The figure is the end of a process with the process thrown away, and once the process is gone the figure cannot be checked, only believed or doubted. Because finance figures get quoted onward, the loss matters more in finance than in most places. Somebody puts an average in a note, somebody else lifts it into a summary, and by the third hop nobody remembers what was counted. A written question survives all three hops.

Three things already settled elsewhere

• The Neelbagh stall record, its faults and the named convention behind its average. The record and its convention were worked out where they belonged, and here they are simply used. Diagnosing the record is covered separately.
• A stored table declares a shape, and the declaring was settled where data sitting in a store was covered. A question can only be precise about a shape that was declared.
• Counting, and one average. The arithmetic goes no further than that: counts of rows, counts of groups, and a single average that never once appears without the count it was divided by.

What are the four parts, and what does each one decide?

Take them one at a time, and take them on something real enough to argue about. Below is the stall returns table exactly as it sits in the store: thirty two rows, one row for each month a stall filed a return, with the columns the questions below are asked about. Every query printed further down runs on these rows and nothing else, so the table repays reading first. A figure appearing later that cannot be found in this table is a fault in the arithmetic and not a fact about markets.

Five rows in that table are shaded, and the reason is worth having now rather than later. Row 11 and row 12 are the same stall month, filed twice: Rs 36,400/- went in on day 5 and Rs 39,700/- replaced it on day 19. Row 14 has a stall, a month and an empty takings box. Row 26 reads 99,999, and 99,999 is not money at all: it is the code the office writes down when no return came in. And row 24 carries Rs 48,000/-, corrected from the day book slip before the first question here is asked, so the typing error that once sat there is a separate subject. The five shaded rows are the whole difficulty here, and they are the reason one question has more than one honest answer.

Now the four parts. The first, what to return, decides the shape of the answer. Ask for one average and one number comes back; ask for a sum and a count alongside it and three numbers come back in one row. From which table decides the grainThe thing a single row of a table stands for. In this table one row stands for one return filed by one stall in one month, and until that sentence can be said out loud the thing being counted is unknown. of what is being counted: what one row actually stands for. Here one row stands for one return filed by one stall in one month, and that sentence is worth saying aloud because every count below rests on it. For which rows decides how many rows are counted, and the number counted fixes the denominatorThe number an average gets divided by. Two people can agree on every rupee and still report different averages, because they disagreed about how many things the money was shared among. underneath any average. And grouped how decides how many answers arrive: one for the whole table, or one for every group named.

-- the whole question, in its four parts, on the table above
SELECT  SUM(takings_rupees)           AS money,
        COUNT(*)                      AS months,
        SUM(takings_rupees)/COUNT(*)  AS per_month
FROM    stall_returns
WHERE   takings_rupees IS NOT NULL
   AND  takings_rupees <> 99999
   AND  NOT (stall_id = 'NB-03'
             AND month = 2 AND filed_on_day = 5);

-- money      1571700
-- months           29
-- per_month  54196.551724...  printed Rs 54,196.55/- over 29

The whole question written out on the thirty two rows above. The query asks for the money and the count in the same breath as the average, and that habit is the one worth keeping.

Notice what that query returns. Not one number but three, and the three arrive together. The money is Rs 15,71,700/-, the count is 29, and only then the average. Asking for the average alone would have been half a line shorter, and that half line is exactly what goes wrong later, so it is worth paying for now. An average handed over without its count is a claim with its evidence removed.

Why is the row filter the question rather than a detail?

The row filter looks like housekeeping. The filter sits in the middle of the query, it is usually the longest part, and it reads like a list of small exclusions somebody added to tidy things up. The filter is not housekeeping. The filter is the question. Everything above it says what arithmetic to do and everything below it says how to slice the result, but the filter decides which rows the arithmetic is done to, and on a record with faults in it that is the only decision that can change the answer by a lot.

The Neelbagh record comes with a conventionA counting rule somebody settled in advance and put in writing, so that whoever follows it later arrives at the same figure rather than a similar one. attached to it, settled earlier and not up for renegotiation here: the average takings per stall month is the sum of the takings cells that carry a usable number, divided by how many carry one. A blank is not a number. The office code for no return received is not a number. A real zero is a number. Peetal Utensils took nothing at all in month two, and month two stays in. The convention does not enforce itself. Somebody has to turn it into a filter, and the filter is where a stated convention becomes an executed one.

-- the row filter alone, and the three rows it turns away
WHERE  takings_rupees IS NOT NULL
       -- turns away row 14, the empty takings box
  AND  takings_rupees <> 99999
       -- turns away row 26, the office no return code
  AND  NOT (stall_id = 'NB-03'
            AND month = 2
            AND filed_on_day = 5)
       -- turns away row 11, the figure later revised

-- 32 rows offered, 3 turned away, 29 counted

The filter on its own, with the row it removes named beside each line. Three rows go, and every one of them goes for a stated reason rather than because it looked odd.

The third exclusion is a different animal from the other two. Look hard at it. Rows 14 and 26 are removed for something visible in the cell itself: one is empty, one holds a value that is not money. A filter can spot both without knowing anything about the market. Row 11 is removed for something that is not in the cell at all. Rs 36,400/- is a perfectly good number. The figure is the right shape, the right magnitude, the right column. The only thing wrong with it is that the office replaced it two weeks later, and the fact that it was replaced lives in the day book rather than in the returns table. A filter can only exclude what somebody already knows, so the row filter carries every piece of knowledge about the record that the record itself does not carry.

And here is the sentence to take away from this whole block. Changing the filter does not refine the answer. A changed filter asks a different question, and a different question is entitled to a different answer. When the filter widens and the figure moves, nothing has gone wrong and nothing has been improved. Something else was asked and something else was answered. The trouble only starts when both figures get written down under the same words.

How many defensible answers does one question have?

Four, on this record, and no amount of care will get it down to one. Here is why. The money is not in dispute. The takings cells that carry usable money add to Rs 15,71,700/-, and there is nothing to argue about in that total because every rupee in it is a figure a stall actually filed. The dispute is over what to divide it by, and that turns out to be a question about what counts as a stall month rather than a question about arithmetic.

The ladder runs from the narrowest filter to the widest, one row at a time. Counting only the cells carrying usable money gives 29, and Rs 15,71,700/- over 29 is Rs 54,196.55/-. Treating a return the office later revised as still a return the office received adds one: that is 30 cells, and the same money over 30 is Rs 52,390/-, exactly, to the rupee. A filter that asks only whether there is a number cannot see that the office code is not money. Treating that row as a row the office wrote a number in gives 31, and the answer is Rs 50,700/-, exactly. Counting every row the office handed over, empty box included, on the ground that a stall month with no figure is still a stall month and simply contributed nothing, gives 32, and the answer is Rs 49,115.62/-.

Two labels belong on that table rather than in a footnote. Both are the kind of thing a reader quietly turns into a finding.

First: all four answers share one numerator, and that is arithmetic rather than agreement. The money staying the same is not a discovery. The money stayed the same because it was held still so that the effect of the count could be seen on its own. In a real argument between two analysts the money usually moves as well, and the two effects then arrive tangled together.

Second: Rs 49,115.62/- is a rounded display, and it is marked as one wherever it appears. Rs 15,71,700/- divided by 32 is 49,115.625 rupees. The half paisa on the end is a quantity no cash box has ever held. The screen has to choose a side and it shows Rs 49,115.62/-. A displayed figure like that looks known to the paisa and is not. The second and third answers, by contrast, really are exact: Rs 15,71,700/- over 30 and over 31 both land on whole rupees with nothing left over. The coincidence belongs to this record and not to thirty and thirty one.

The falls themselves are worth a second. Going from 29 to 30 the answer falls Rs 1,806.55/-. From 30 to 31 it falls Rs 1,690/-. From 31 to 32 it falls Rs 1,584.38/-. The three falls add to Rs 5,080.93/-, the distance from end to end, so the ladder reconciles in both directions. The answer never holds still at any step, and it never turns round. The steady fall is not a general property of averages either; it happens here because each row added carries no money with it, so every step divides the same amount among more things.

One more thing to name and then leave alone. The table holds 32 rows, and eight stalls across four months would also come to 32 stall months. The two counts agree by accident: one stall month is missing from the record entirely and one stall month was filed twice, and the two cancel. Treat that as arithmetic on this particular record. An identity that happens to hold in one case is not a rule, and reading it as one is how a coincidence gets written up as a finding.

Wherever that control is set, the money underneath it never changes, and yet the figure on the readout moves by Rs 5,080.93/- from one end to the other. Every one of those four readings is defensible, every one is arithmetically correct, and every one looks exact enough to quote. Precision is not the scarce thing here; agreement about the count is.

What does grouping inside a query give?

So far every question has come back as one number for the whole market. Grouping is the instruction that says: do not give me one answer, give me one answer for each of these. Ask the same question grouped by the stall and eight answers come back, one for each stall still trading. Ask it grouped by the stall name and nine come back. There are eight stalls. The ninth answer is not a stall.

-- the same question, now one answer per stall
SELECT  stall_id,
        COUNT(*)                      AS months,
        SUM(takings_rupees)/COUNT(*)  AS per_month
FROM    stall_returns
WHERE   /* the same row filter as before */
GROUP BY stall_id;      -- 8 answers

-- change one word, stall_id to stall_name, and 9 come back:
--   Harit Greens  2 months  Rs 38,850/-
--   Harit Green   2 months  Rs 40,500/-
-- the stall itself, over all 4 months, answers Rs 39,675/-

The same question grouped two ways. One word changes and the count of answers goes from eight to nine, with no warning of any kind from anything.

Harit Greens is written Harit Greens on its month one and month two rows and Harit Green on its month three and month four rows. Group by the name and the store does exactly what it was told: it finds two distinct values, so it builds two groups, and it reports Rs 38,850/- for one and Rs 40,500/- for the other. Both figures are arithmetically perfect. Neither is the answer for that stall. Over all four of its months the stall answers Rs 39,675/-. And nothing anywhere raised so much as a warning. From the store's side nothing unusual happened at all.

The count of answers is a check on the column before it is a result. Eight stalls should give eight groups. Nine groups means the column grouped on has more distinct values than the thing meant to be grouped. The check costs one glance, and it is the only thing standing between an analyst and a report in which one stall appears twice under two spellings. The same check mattered when the table was held in memory rather than in a store, and it matters for the same reason: a readable column is not a reliable one.

Which is why the grouping goes on the identifierA code that exists purely to point at one particular thing. It is a label wearing the clothes of a number, so a column of them adds up to a total that means nothing at all. rather than on the name, even though nobody enjoys reading a column of codes. An identifier is written once and applied mechanically. A name is retyped by a person every time a return is filed. Group on stall_id and eight groups come back. The name is for the reader. The identifier is for the grouping.

What is the routine for asking a record a question?

Everything above collapses into six steps, and they are worth following in order even when the question feels too small to deserve a routine. Especially then. The figures that end up quoted onward are almost never the ones somebody treated as important at the time.

How to Query Financial Data With SQL, in six steps

1. Say what one row of the table describes, in a full sentence. Here: one row is one return filed by one stall for one month. Where that sentence cannot be finished, the work stops there. Every count produced afterwards would be a count of something unnamed.
2. Say which rows are being kept, and say why. Not which rows are being dropped. Keeping is the positive statement, and it forces a defence of the boundary rather than an apology for the exclusions.
3. Say what the count underneath the answer is going to be, before the query runs. Write the number down. Twenty nine, in the worked case above.
4. Run it, and read that count first. If it is not the number written down in advance, something has been learned about the record and the average can wait.
5. Read the answer second. An average over the wrong number of things is a correct calculation of an irrelevant quantity, so the answer means nothing until step four has passed.
6. Write the question down beside the answer and keep the two together. Not in a separate file, not in a message thread. Beside it.

Step three is the one people skip, and skipping it converts step four from a check into a formality. Without a number committed to in advance there is nothing to compare against, so whatever count comes back looks reasonable. Twenty nine looks fine. Thirty looks fine. Thirty two looks fine. Every one of them looks fine right up to the moment somebody else runs a slightly different filter and gets a figure Rs 5,080.93/- away from the first.

Step six sounds like paperwork and is actually the whole of provenanceThe paper trail behind a figure: which record it began in, and what happened to it between there and here. No system keeps it, so it survives only if a person writes it. for a figure like this. Nothing in any store keeps it. The store returns numbers; the record of what was asked exists only if a person creates it. A lender reading a borrower's monthly figures, an analyst rebuilding a series from a raw file, a household comparing this year's spending to last year's: in every one of those cases the figure is the easy part and the question behind it is what somebody will want three months later and will not be able to reconstruct.

What will a query not say?

Three things. Every one of them is a silence rather than an error, and a silence is what makes them expensive.

A query will not say that a different filter was equally defensible. A query answers exactly what it was asked and has no concept of what else might have been meant. A request for the average over 29 rows returns the average over 29 rows, delivered with the same flat confidence whether that was the right 29 or not. There is no warning available here, not because the software is unhelpful but because the alternative filters exist only in the heads of the people who might have written them.

A query will not say where the figures came from. The store hands back the values it holds. The store does not hand back the fact that row 24 was corrected against a day book slip, or that row 11 was superseded two weeks after it was filed. The history is not a column, so it is not in the answer, and no amount of querying will produce it.

And the sequence the rows arrive in belongs to the store that assembled them rather than to the market they describe. The row orderThe sequence rows turn up in. It reflects how this particular answer was assembled, not anything about the market, and asking the same thing twice can return two different sequences. on display is an artefact of how the store chose to assemble the answer this time. Reading a ranking off it, or taking the first row to be the earliest month, makes a claim about the market on the basis of something that could come back differently tomorrow with nothing having changed.

Precision and correctness are different properties, and a query supplies only the first. A query will give a figure to as many decimal places as anyone likes, computed without a single arithmetic error, from exactly the rows named. Whether those were the right rows is a question about the market, the convention and the purpose, and none of those three is inside the table. The honest limit of the tool sits exactly there, and knowing it is what separates somebody who can run a query from somebody who can be trusted with the answer.

What must never be printed without its count?

The rule is not for analysts only. Think about how a household argues over what it spends a month on food. One person counts the twelve months of the year; another counts the ten months anybody actually kept the receipts; a third counts everything including the wedding month, and the wedding month was not a normal month at all. The total is the same slips in a drawer. The three of them are not disagreeing about money, they are disagreeing about the count, and the argument only ends when somebody says out loud what they divided by. A lender comparing two borrowers, an analyst comparing two periods, a fund administrator comparing two months: it is always the same argument, and it always ends the same way.

What goes wrong when the count is left off?

What is covered elsewhere?