The SUM(col) of zero rows should zero, but is instead NULL

I must admit I get bitten by this regularly.

The SQL standard states that aggregate functions should return NULL if no rows are processed (with the exception of COUNT).

This means that computing the SUM of zero rows returns NULL as opposed to 0.

This goes against mathematical intuition. If I have zero USB sticks, how much data can I store on my USB sticks? The answer is 0 bytes, but modelling USB sticks in a database would return "undefined" number of bytes can be stored on my zero USB sticks.

mysql> desc email_box;
| box_size_bytes | int(10)     |

mysql> select count(*) from email_box;
|        0 |

mysql> select sum(box_size_bytes) from email_box;
| NULL |

This article is © Adrian Smith.
It was originally published on 29 Nov 2006
More on: FAIL | Databases | Language Design