I'm attempting to figure out why certain DBMS systems allow the following but others don't. Assume table X contains the following attributes: name, id, and data.
Code:
SELECT id, count(*) as count
FROM TABLE X
GROUP BY id
HAVING count > X.data
It is forbidden in most databases to utilize a non-grouping or non-aggregate field in a HAVING clause conditional statement. Some systems appear to permit the same. Could you explain why they would have permitted the HAVING condition to utilize a property that may not have a unique value throughout the group? I found some useful info but need further explanation. Database documentation for DB2, PostgreSQL, and MySQL was consulted.
Code:
SELECT id, count(*) as count
FROM TABLE X
GROUP BY id
HAVING count > X.data