The help gives some examples of what constitutes an inappropriate use of an aggregate function :
I can certainly think of situations where you would get undesirable results, but I'm hoping someone could provide a better explanation as to the "Why" these uses are inappropriate.
asked 27 Jan '12, 15:20
The most straightforward example is to use an aggregate function in a WHERE clause, ie
SELECT FROMWHERE COUNT() > 5 GROUP BY <table.column>
In SQL, a SELECT statement's semantics are (roughly) as follows:
I said "roughly" because grouping, semantically-wise, is actually a fairly complicated thing to describe completely and precisely, particularly due to the difference in results when the query has a GROUP BY clause, and when it does not. Chris Date, in his book An Introduction to Database Systems, has a number of really lovely examples where he takes those semantics to task, but SQL is what it is...
Anyway - from those semantics you can see that including COUNT() in a WHERE clause is semantically invalid - the value of COUNT(*) is computed during the grouping step, so trying to apply the predicate beforehand during the selection (filtering) process is incorrect. Instead, one should move the predicate to a HAVING clause, so that the condition is tested after the groups are formed:
When the language includes WINDOW functions (and SQL Anywhere does support WINDOW queries) then the semantics are even more difficult to define and explain, but they do make sense - if you don't try to think about it too hard - even though I've yet to see a formal definition (in the mathematical sense) of WINDOW.
answered 27 Jan '12, 16:20
UPDATE t SET SUM ( c ) = 0;
...how would you like that done? Set c to zero in every row? Set alternating rows to random positive/negative numbers so the sum remains zero?
CREATE TABLE t ( c INTEGER CHECK ( AVG ( c ) = 0 ) ...
...what is the aggregation over which the AVG is calculated? Is it recalculated and rechecked with each row insertion/update/deletion?
It is likely that each case where that error message appears is somewhat different. Some may fall into the category "Engineering hasn't figured out how to support this usage" but most are probably "
Perhaps you have run into a situation where the message doesn't seem right; if so, please share it with us.
answered 27 Jan '12, 16:30