# 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.