Re: PostgreSQL v Informix

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. Informix's use of NULLs makes perfect sense to me, but then if I add up my knowledge on the subject, I end up with a NULL result. ;-) Cheers, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+