Interpret end of data with aggregate functions
A SELECT statement that uses an aggregate function such as SUM, MIN, or AVG always succeeds in returning at least one row of data, even when no rows satisfy the WHERE clause. An aggregate value based on an empty set of rows is null, but it exists nonetheless.
However, an aggregate
value is also null if it is based on one or more rows that all contain
null values. If you must be able to detect the difference between
an aggregate value that is based on no rows and one that is based
on some rows that are all null, you must include a COUNT function
in the statement and an indicator variable on the aggregate value.
You can then work out the following cases.
| Count Value | Indicator | Case |
|---|---|---|
| 0 | -1 | Zero rows selected |
| >0 | -1 | Some rows selected; all were null |
| >0 | 0 | Some non-null rows selected |