Re: sum() - unexpected results.
От | Tod McQuillin |
---|---|
Тема | Re: sum() - unexpected results. |
Дата | |
Msg-id | Pine.GSO.4.31.0102052328510.6542-100000@sysadmin обсуждение исходный текст |
Ответ на | sum() - unexpected results. (Jeff MacDonald <jeff@hub.org>) |
Список | pgsql-general |
On Mon, 5 Feb 2001, Jeff MacDonald wrote: > I got some unexpected results from a sum().. just wondering > if it's a bug of any sort, or if i just should have been expecting it.. > > SELECT sum(foo) FROM mytable WHERE active = 1; > > Now lets say that no rows are active = 1, then this query returns > 1 blank row.. It is returning NULL, which is what sum() returns when it is asked to sum 0 rows. > I would have expected either > 1: a result of zero > or > 2: zero rows returned.. If you want a result of zero, use SELECT coalesce(sum(foo), 0) ... This is the way SQL-92 says it should be. On page 126 of the document at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt it says: b) If AVG, MAX, MIN, or SUM is specified, then Case: i) If TXA is empty, then the result is the null value. (TXA refers to the table constructed from the summed column). -- Tod McQuillin
В списке pgsql-general по дате отправления: