Re: PostgreSQL v Informix

In article <EBHt7y.IMC@inform.co.nz>, david@inform.co.nz (David Paulo) wrote: >In article <3394902B.769B@west.co.za>, "Mark D. Stock" <marks@west.co.za> writes: >> Kerry Sainsbury wrote: >> > >> > While Informix does return a NULL on a SUM of no rows, and I assume that's >> > in the SQL standard somewhere, can anybody explain to me *why*? >> > >> > If my question is: >> > >> > SELECT SUM(quantity_sold) FROM orders WHERE city = "Tokoroa" >> > >> > and I've not sold anything in Tokoroa, shouldn't the answer be zero? >> > The answer isn't "undefined" or "unknown", is it? Hmmm, maybe it *is* >> > undefined, but it still doesn't really make sense to me. >> >> If you have no record of sales in "Tokoroa", then the total is >> "unknown". If you had records detailing zero sales in "Tokoroa", >> then yes, the total would be zero. > >Seems to me that by that logic ALL answers should be NULL as you are assuming that the database cannot be depended upon as a complete record of sales? The thought that helped me accept the standard implementation was if I changed the SQL to work on listing temperatures, rather than quantity sold: SELECT MAX(temperature) FROM temps WHERE city = "Tokoroa" If I've never received any readings from Tokoroa then I don't want a value of zero returned (although at this time of year it might be accurate :-) ------------------------------------,------------------------------------------ Kerry Sainsbury, kerry@kcbbs.gen.nz | THE INFORMIX FAQ v2.10 May 1997 Quanta Systems, Auckland | http://www.iiug.org/techinfo/faq/ New Zealand. Work: +64 9 377-4473 | ftp://ftp.iiug.org/pub/informix/faq Home: +64 9 279-3571 | ftp://kcbbs.gen.nz:/pub/informix/