So your

is all good standard debate, is it? Some of it looks remarkably like personal abuse to me. My view is that you started dishing it out and so I started handing it back, so now we are both doing it (which is rather sad) and your "only one" is therefore inaccurate since we are two.

and the passage I just quoted suggests that the only one using a lie is you, since your "only one" is clearly intended to designate me, and the quoted passage shows you at it. But rather than calling you a liar I would call it a mistake you made in the heat of the moment. Accusing you of "outright blatant lies", whether because I disagree with you or because what you say is plainly untrue or both, would attribute to you a motivation for which there is no evidence - and I won't take personal abuse down to that level (and would have more respect for you if you would refrain from it too).

You are repeating yourself, and just as mistakenly as before.

You perhaps didn't notice that I said that the technique works only under specific circumstances, not that it would never work. The reason it doesn't work under other circumstances is that it makes no attempt to control the cumulative error, which can be very important when there are millions of additions. The specific circumstances in which it works are that there is a datatype capable of representing exactly every datum and every intermediate result (there will probably be error introduced by the division operation even then, but there's no escaping that except by luck and that is in any case a small rounding error introduced by a single divide op). So, for example, if you have 10 million numbers with an average value of about 10^29 you can't do it because the sum will overflow numeric(38,0), bigint, and any other exact numeric type SQL or most other languages have - you have to use FLOAT to do the arithmetic. So now you are into inexact numerics, and have introduced representation errors as SQL uses the wrong floating point base for accurate representation of our usual decimal number system, so you don't want to introduce yet more errors, and you will use some error reduction technique which is likely to involve something like sorting the numbers (on magnitude, ignoring sign) and eliminating cancelling pairs before doing any arithmetic other than picking up the count to divide by, and then doing the additions in the sorted order (increasing order of magnitude). SQL Server will use the error-ignoring method even when the original data is floating point, which is unfortunate and is why some scientific and engineering projects either pull the data out of the database to compute aggregates, instead of using the built-in capability, or write their own DBMS that doesn't have the problem.

And the technique doesn't work because, again, it does no error reduction when it has to deal with inexact numerics. Of course it would work any time computing the average using the no-error-avoidance worked because everything could be kept exact and would still have worked if the data had been presented in the inverse order to its subsequent removal. But when inexact arithmetic is needed, or when the original data is held in an inexact format, unless the values are removed in the inverse order to that in which they were added to the total when calculating the average of all vales, it could lead to very inaccurate results indeed. I don't think anything I said misrepresented anything you wrote. We both know perfectly well that MS doesn't use that technique for calculating a modified average, because they don't compute modified averages at all. If one wanted to compute them with reasonably small errors then a lot of resource would be required, because after some threshold of values had been removed one would need to compute the average from scratch to stop accumulating error, and that computation involves (as we are looking at inexact data) a sort of all the (remaining) data. So my statement that an awful lot of compute power would be required was perfectly correct (unless one arbitrarily refuses to take account of the need to do error reduction when handling inexact values). I don't think my comments about the error-accumulation potential of the method were in any way misrepresenting what you said, in fact I can't imagine how you could interpret them as doing so, and at first I took your use of the word "deliberately" in that context as an intentionally vicious unjustifiable slur. On cooler reflection, I shall just regard it as another unfortunate mistake in the heat of the moment.

I described some work background because it is clearly relevant to whether I'm aware that producing and using a spool can be an expensive operation - something you had claimed, with no apparent justification at all, that I clearly didn't understand. I mentioned some education background to indicate how old the spool concept is - since you appeared to think it was something newly invented for databases at some time in the 70s.

I must have missed that one somehow - which message was it in?

Well, as far as I can see neither of us has actually applied the word "stupid" to the other. Several things you have said clearly suggest (and have clearly been intended to suggest) that I'm an ignorant and arrogant person who pays no attention to other people's arguments, and as I would regard anyone who displayed those qualities as utterly stupid that I think is the nearest either of us has come to insinuating stupidity.

Well, I disagree. You posted a comment with some very offensive and uncivilised remarks in it, and I then responded in kind. Unlike you I recognised that I was being uncivilised (although I don't think I ever got down quite to your level - that "deliberately misrepresents" really is the pits, "not going to be open-minded enough to actually put thought into what I have to say" was almost as bad, and "outright blatant lies" is extremely nasty too - you won't find anything as offensive as any of those in my comments.

Anyway, I'm convinced this isn't the proper forum for a flame war, so I don't think I'll particpate in any more of this silliness.