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

By Adrian Smith29 Nov 2006200 words1 mins to read

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 was written by Adrian Smith on 29 Nov 2006

Follow me: Facebook | Twitter | Email

More on: FAIL | Databases | Language Design