Mastering Oracle SQL

The GROUP BY Clause

The GROUP BY clause, along with the aggregate functions, groups a result set into multiple groups, and then produces a single row of summary information for each group. For example, if we want to find the total number of orders for each customer, execute the following query:

            SELECT CUST_NBR, COUNT(ORDER_NBR) 
            FROM CUST_ORDER 
            GROUP BY CUST_NBR;

  CUST_NBR COUNT(ORDER_NBR)
---------- ----------------
       201                2
       231                6
       244                2
       255                6
       264                2
       288                2

6 rows selected.

The query produces one summary line of output for each customer. This is the essence of a GROUP BY query. We asked Oracle to GROUP the results BY CUST_NBR; therefore, it produced one output row for each distinct value of CUST_NBR. Each data value for a given customer represents a summary based on all rows for that customer.

The nonaggregate expression CUST_NBR in the SELECT list also appears in the GROUP BY clause. If we have a mix of aggregate and nonaggregate expressions in the SELECT list, SQL expects that we are trying to perform a GROUP BY operation, and we must also specify all nonaggregate expressions in the GROUP BY clause. SQL returns an error if we fail to do so. For example, if we omit the GROUP BY clause, the following error is returned:

            SELECT CUST_NBR, SALES_EMP_ID, COUNT(ORDER_NBR)
            FROM CUST_ORDER;
SELECT CUST_NBR, SALES_EMP_ID, COUNT(ORDER_NBR)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Similarly, if we forget to include all nonaggregate expressions from the SELECT list in the ...