Unlike SQL:92 which provides limited aggregation functionality, SQL:1999 standard defines a rich set of aggregate functions. Most of these functions are supported by Oracle and IBM DB2, and other databases will support these functions in the near future. SQL:1999 provides some extensions to basic
GROUP BY clause using
CUBE clauses. It also provides a set of new analytic functions that provide a means by which pivot reports and OLAP queries can be computed easily in nonprocedural SQL.
Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins and nested queries, which were difficult to write and inefficient to execute. SQL analytic functions ...