Oracle Group By Clause

This SQL tutorial focuses on the Oracle Group By Clause, and provides explanations, examples and exercises. For this lesson’s exercises use this link.

This tutorial is a part of several posts explaining how to use the Oracle Group Functions. To read additional posts regarding this subject, please use the following links:

• Oracle group functions
• Oracle Group By clause
• Oracle Having clause

The Oracle GROUP BY Clause

SELECT       column_name , group_function(column_name)
FROM         table_name
WHERE        condition
GROUP BY     column_name

So far, each group function described in the previous tutorial has treated the table as one large group of data. In most cases you need to divide the table into smaller groups, instead of getting the average price of all products in products table, you would rather see, for example, the average price grouped by each category (what is the average price of the “Sea Food” category, “Beverages” category and so on).

You can use the Oracle GROUP BY clause to divide the rows in a table into groups. Then you can use the group functions to retrieve summary information for each group.

This Oracle example would retrieve a summary of the average price for each category number:

SELECT      category_id , AVG(unit_price)
FROM        products
GROUP BY    category_id

A Few Guidelines

All columns in the Oracle SELECT clause that are not group functions must be in the GROUP BY clause

When specifying group functions (such as AVG) in the SELECT clause, alongside with other individual items (such as category_id), you must include a GROUP BY clause. In the GROUP BY clause you must specify these individual items (category_id in this case) otherwise an error will be generated.

The Oracle GROUP BY columns don’t have to be in the Oracle SELECT clause

It is absolutely possible to group by different columns, but not to specify these columns in the Oracle SELECT clause (however the result will not be meaningful). This Oracle example would retrieve a summary of the average unit_price for each category without displaying the respective category numbers.

SELECT      AVG(unit_price)
FROM        products
GROUP BY    category_id

Sometimes you need to see the result for groups within groups, for example: each category is provided by different suppliers (“Sea food” products are provided by 4 different suppliers). While the Oracle examples carried out so far were meant to display the average of each category, the query specified below retrieves a summary of the average price for each category and for each supplier:

SELECT      category_id , supplier_id , AVG(unit_price)
FROM        products
GROUP BY    category_id , supplier_id

Using the Oracle WHERE clause, you can exclude rows before dividing them into groups.

For example, if you need to display the average price only for categories 50, 80, and 90:

SELECT      category_id , AVG(unit_price)
FROM        products
WHERE       category_id IN (50, 80, 90)
GROUP BY    category_id

As seen in the last example, using the Oracle WHERE clause you can restrict rows before dividing them into groups. However, it is not possible to specify group functions in a Oracle WHERE clause, as that would result in an error.

The categories where the average unit_price is higher than 50:

 SELECT      category_id , AVG(unit_price)
 FROM        products
 WHERE       AVG(unit_price) > 50
 GROUP BY    category_id    

-- (error)