On May 22, 7:36 am, Jerry Stuckle <jstuck...@attg lobal.netwrote:
Tim Roberts wrote:
No, in this case, Jerry is right and you are wrong. In strict ANSI SQL,
any column reference in the HAVING clause must either be part of the result
set or one of the grouped columns. "groupid" is not present in the result
set, nor is it grouped.
Tim,
Don't bother arguing with the idiot. He'll never admit that he's wrong.
He's even quoting a standard that has been out of date for over 10 years!
This query is valid in every version since SQL-92.
The aggregate function aren't restricted to referencing
only grouping columns.
This part defines the relevant restriction on column
references in the HAVING clause:
Subclause 7.10, Syntax Rules, #3: "Each column
reference directly contained in the search condition
shall be one of the following: a) An unambiguous
reference to a column that is functionally dependent
on G. b) An outer reference."
[SQL/Foundation 2003/08]
This rule doesn't apply to columns in aggregate
functions because they aren't directly contained,
i.e.:
Subclause 6.3.3.1: "A1 directly contains B1 if A1
contains B1 without an intervening subquery, multiset
value constructor by query, table value constructor
by query, array value constructor by query, within
group specification, or set function specification
that is not an ordered set function."
[SQL/Framework 2003/08]
SQL-92 has a similar definition. Mitch Sherman was
incorrect in quoting the part about subqueries because
there is no subquery being used, but the query is still
valid for the reason he implied.
Aggregate functions like COUNT are set function
specifications and hence don't fall under rule #3
because the column references are not directly
contained in the HAVING condition. These aggregate
functions can reference any column in the argument
source, i.e.:
Subclause 6.7: "If QCR [query column reference] is
a within-group-varying column reference, then QCR
denotes the values of C in the rows of a given group
of the qualifying query of QCR used to construct the
argument source of a set function specification."
[SQL/Foundation 2003/08]
The argument source is a table or a group containing
N rows.
One example using this would be finding the customers
who transacted more than a certain amount in sales:
SELECT cust_id
FROM sale
GROUP by cust_id
HAVING SUM(amount) 500
There are some more examples of this pattern here:
http://www.cs.jcu.edu.au/Subjects/cp...html#section48 
