Kareem Badr wrote: > Bill KING wrote: >> Kareem Badr wrote: >> >>> I should mention that free_space and size are defined as varchars, for >>> some reason. Not sure if that has anything to do with the issues I'm >>> seeing. >>> >>> >> Do any of these fields contain NULL? as that will definately have an >> impact upon both joins and upon calculations. (when using a numeric >> field, it's always better to set the field NOT NULL, and default it >> to 0). >> >> > Some of the fields contain NULL, yes, but not in the row that is > returned by the query that is returning a single row. > > Best I can figure out is that, for whatever reason, when a single row > is returned, the division results in 0. When more than 1 row is > returned (even if the row has nulls in the field...which one of the > rows does), the division works. > > Here are the contents of the table: > > C: | 71975571456 | 79966171136 > D: | | > G: | 206140624896 | 237003182080 > > > I also noticed that in the case where the division worked, the sums > are displayed as floats in the resulting row. Very odd. > >From what I remember of aggregates, if there is a NULL in the calculation on any field, the result will always be NULL. This is a huge beware. I'm not sure if it's affecting what you're doing, but I have a clue that it may be. How are the results affected when you
update devices set free_space = 0 where free_space is NULL; update devices set size = 0 where size is NULL; and rerun the two initial queries again? -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
