Oracle SQL - Valid SELECT and GROUP BY Clause Combinations

  1. HOME
  2. Oracle SQL
  3. Select
  4. Group Functions

Introduction

If your queries have a GROUP BY clause, some syntax combinations are invalid and result in Oracle error messages.

You may get the following error message. 

ORA-00937: not a single-group group function.

It means that there is a mismatch between your SELECT clause and your GROUP BY clause.

The following table lists valid versus invalid syntax.

Syntax Valid?
select a, b, max(c) from t ... group by a No
select a, b, max(c) from t ... group by a,b Yes
select a, count(b), min(c) from t ... group by aYes
select count(c) from t ... group by a Yes

Here are the two general rules:

  • You do not need to select the column expression you group on (last example).
  • Any column expression that is not part of the GROUP BY clause can occur only in the SELECT clause as an argument to a group function.

Related Topic