Some SELECTs will never return 0 results -- regardless of the criteria

In SQL, the general rule of thumb is that the number of rows returned from a SELECT will be zero if your criteria did not match any data. However, there is an important exception to this rule; it does not apply when asking for aggregate calculations such as SUM(), MIN() or MAX(), without any grouping. This is rather interesting and important to know and look out for, as it can cause some confusion and recently some of my ASP.NET code failed due to this. Let's take a look.