Entries for September 2007
September 11, 2007 at 2:56PM SQL Tricks: SUM(), then COALESCE()
Seeing as I’m after doing this for the umpteenth time just now, I thought I’d note it here. There’s no chance of me forgetting it, but somebody who doesn’t know the trick might find it useful.
I have to write quite a few reports where I need to, say, count the total number of items and then the number of items that fit certain criteria. My favourite trick for reports like that is to SUM() on a boolean expression specifying the criteria, then COALESCE() the result with 0.
For instance, let’s say you’ve a table containing customer invoices, and you want to get the total number of invoices and the number paid invoices, and the number of unpaid invoices with more than 20EUR left to pay on them. To do that, you’d do something like this:
SELECT COUNT(*) AS invoices,
COALESCE(SUM(amount_paid = amount_total), 0) AS paid,
COALESCE(SUM(amount_total - amount_paid > 2000), 0) AS spongers
FROM invoices
The way this works is that boolean expressions in SQL evaluate to either 1 or 0. Because of this, summing what boolean expressions evaluate to can tell us how many records match that expression.
The presence of COALESCE() is to counter an edge case where the result you’re summarising contains no row. This might be because the table’s empty, the criteria specified in the WHERE clause doesn’t match anything, &c. If this is so, SUM() will return NULL, but what we really want is 0, so COALESCE() is in there to catch this.
September 28, 2007 at 2:49PM And now for something completely different...
I’m working on a monologue for a story I’m writing. Here’s what I have right now. The story’s metaphysical sci-fi. The character making the speech is meant to speak in a rather flowery and grandiloquent manner, so ignore that. What I want to know is if the character’s line of thought could hold together any better. Suggestions?
“All that has been will be again! All that you have done, you will do again! The threads that bind must be woven! This is inescapable. Without them, the pattern falls apart. The thread can take whatever path so long as the stitch is strong and true. This is the great shame and glory of those that bind the pattern: they are least free because they are needed most. Such is life: that which binds always suffers most.
“That which binds never knows when it must suffer, or how. In a manner of speaking its very existence is suffering; but all suffering can be transcended, and a thread’s strength comes not only from itself but that which it binds. Hold strong, and all around you will hold you strong. Such is life: strength comes from suffering, but strength leads to transcendence of suffering.
“However, without true realisation, strength is limited. That is why you are here, to learn. But I am not here to teach, simply to reveal, as blind acceptance is not true realisation. There is no single doctrine, no ultimate truth, no salvation from without. You must realise your path, from where you came from to where it will end and start again. We cannot share the same path. Such is life: it is our own free will that binds us to our path.
The point is to square free will with the predestination of certain people in the universe without boring the reader. One such person is the character the speech is directed at.
