Oracle SQL SELECT: GROUP BY clause

Aggregate functions

With the having clause, rules can be formulated that specify which (aggregated) rows are returned.

group by extensions

The execution plan operations

• HASH GROUP BY
• SORT AGGREGATE

Oracle does not allow to group by LOB columnns.

Oracle recommends to use deterministic user defined PL/SQL functions if they're used in group by clauses.

The SQL group by clause.

The SQL work area in the PGA.

The pivot clause in an select statement.

The behavior of group by is influenced by the value of nls_sort.

The error messages

• ORA-00932: inconsistent datatypes: expected - got CLOB (which is raised when trying to group by on a clob or blob value)
• ORA-00933: SQL command not properly ended error message
• ORA-00937: not a single-group group function
• ORA-00979: not a GROUP BY expression
• ORA-01787: only one clause allowed per query block

GROUP BY extensions introduced with 21c.

The _optimizer_extend_jppd_view_types init parameter

Using group by in a view prevents the view from being updatable.