Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Summarizing, Grouping, and Sorting Query Results » Understanding GROUP BY

Understanding GROUP BY Next Page

How queries with GROUP BY are executed

This section uses the ROLLUP sub-clause of the GROUP BY clause in the explanation and example. For more information on the ROLLUP clause, see Using ROLLUP and CUBE.

Consider a single-table query of the following form:

SELECT select-list
FROM table
WHERE where-search-condition
GROUP BY [ group-by-expression | ROLLUP (group-by-expression) ]
HAVING having-search-condition

This query can be thought of as being executed in the following manner:

  1. Apply the WHERE clause    This generates an intermediate result that contains only some of the rows of the table.

    The intermediate result contains a subset of rows from the table.
  2. Partition the result into groups    This action generates an intermediate result with one row for each group as dictated by the GROUP BY clause. Each generated row contains the group-by-expression for each group, and the computed aggregate functions in the select-list and having-search-condition.

    A second intermediate result is made smaller by the application of the GROUP BY clause.
  3. Apply any ROLLUP operation    Subtotal rows computed as part of a ROLLUP operation are added to the result set.

    For more information, see Using ROLLUP.

  4. Apply the HAVING clause    Any rows from this second intermediate result that do not meet the criteria of the HAVING clause are removed at this point.

  5. Project out the results to display    This action takes from step 3 only those columns that need to be displayed in the result set of the query-that is, it takes only those columns corresponding to the expressions from the select-list.

    The final result set is a projection of the second intermediate result set.

This process makes requirements on queries with a GROUP BY clause: