firebird/doc/sql.extensions/README.aggregate_tracking

SQL Language Extension: Improved aggregate handling

Syntax
========


GROUP BY
--------
SELECT ... FROM .... [GROUP BY group_by_list]

group_by_list : group_by_item [, group_by_list];

group_by_item : column_name
		| ordinal
		| udf
		| group_by_function;

group_by_function	: numeric_value_function
		| string_value_function
		| case_expression
		;

numeric_value_function	: EXTRACT '(' timestamp_part FROM value ')';

string_value_function	:  SUBSTRING '(' value FROM pos_short_integer ')'
			| SUBSTRING '(' value FROM pos_short_integer FOR nonneg_short_integer ')'
			| KW_UPPER '(' value ')'
			;

(FB1.0 only allowed GROUP BY column_name and udf)
Ordinal references to a n-th select-item from the select-list (same as ORDER BY).
The group_by_item cannot reference to any aggregate-function (also not burried 
inside a expression) from the same context.


HAVING
--------
The having clause only allows aggregate functions or expressions 
that are part of the GROUP BY clause. Previously it was allowed
to use columns that were not part of the GROUP BY clause.


ORDER BY
--------
When the context is an aggregate statement then the ORDER BY 
clause only allows valid expressions. That are aggregate 
functions or expression part of the GROUP BY clause.
Previously it was allowed to use non-valid expressions.


Aggregate functions inside sub-selects
--------
It is now possible to use a aggregate function or expression
contained in the GROUP BY clause inside a sub-select.
(See Examples A)


Mixing aggregate functions from different contexts
--------
You can use aggregate functions from different contexts inside 
a expression.
(See Example B)


Sub-selects are supported inside a aggregate function
--------
Using a singleton select expression inside a aggregate function is
supported.
(See Example C)


Nested aggregate functions
--------
Using a aggregate function inside a other aggregate function is
possible if the aggregate function inside is from a lower context.
(See Example C)


Author:
    Arno Brinkman <[email protected]>


N O T E S
=========

 - Not all expressions are currently alowed inside the GROUP BY list.
   (concatenation for example) 
 - ORDER BY clause only accepts valid expressions (this was not for FB1.0)
 - HAVING clause only accepts valid expressions (this was not for FB1.0)
 - Using ordinal 'copies' the expression from the select list as does the
   order by clause. This means when a ordinal references to a sub-select
   the sub-select is at least executed twice.


Examples
========


A)
  SELECT
    r.RDB$RELATION_NAME,
    MAX(r.RDB$FIELD_POSITION),
    (SELECT 
       r2.RDB$FIELD_NAME 
     FROM 
       RDB$RELATION_FIELDS r2
     WHERE 
       r2.RDB$RELATION_NAME = r.RDB$RELATION_NAME and
       r2.RDB$FIELD_POSITION = MAX(r.RDB$FIELD_POSITION))
  FROM
    RDB$RELATION_FIELDS r
  GROUP BY
    1

  SELECT
    rf.RDB$RELATION_NAME AS "Relationname",
    (SELECT
       r.RDB$RELATION_ID
     FROM
       RDB$RELATIONS r
     WHERE
       r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME) AS "ID",
    COUNT(*) AS "Fields"
  FROM
    RDB$RELATION_FIELDS rf
  GROUP BY
    rf.RDB$RELATION_NAME


B)
  SELECT
    r.RDB$RELATION_NAME,
    MAX(i.RDB$STATISTICS) AS "Max1",
    (SELECT
       COUNT(*) || ' - ' || MAX(i.RDB$STATISTICS)
     FROM
       RDB$RELATION_FIELDS rf
     WHERE
       rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME) AS "Max2"
  FROM
    RDB$RELATIONS r
    JOIN RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
  GROUP BY
    r.RDB$RELATION_NAME
  HAVING
    MIN(i.RDB$STATISTICS) <> MAX(i.RDB$STATISTICS)

Note! This query gives results in FB1.0, but they are WRONG!


C)
 SELECT
    r.RDB$RELATION_NAME,
    SUM((SELECT
           COUNT(*)
         FROM
           RDB$RELATION_FIELDS rf
         WHERE
           rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME))
  FROM
    RDB$RELATIONS r
    JOIN RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
  GROUP BY
    r.RDB$RELATION_NAME