Re: [HACKERS] SUM() and GROUP BY

Thus spake Tom Lane
> "Thomas G. Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> I disagree ... the sum of zero items has traditionally been defined as
> zero by any mathematician you care to ask. No logical problems are
> introduced by doing so, and it avoids an unpleasant special case that
> applications would otherwise be forced to deal with. (Example: if
> D'Arcy's tramount column has been declared NOT NULL, then it seems to me
> that his code is entitled to expect to get a non-NULL result from SUM().
> He should not have to cope with a NULL just because the table is empty.)

Or, if I add a "WHERE tramount IS NOT NULL" clause to force it to add
non-null items, right? It should depend on the values in the selected
set, not the values in the full table.

> You do have to take it on a case-by-case basis, though. For example
> AVG() of no items should (and does) return NULL, because the average
> of no items is not defined (since 0/0 is indeterminate).

What about GROUP BY though.

SELECT client_id, AVG(tramount) FROM acctrans
WHERE client_id = 'NOBODY' GROUP BY client_id;

It's not that I want a special value returned, I want no rows returned.
Currently it returns 1 row even thought 'NOBODY' doesn't exist in the
database.

--
D'Arcy J.M. Cain <darcy(at){druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.