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:
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
You can list more than one column after the Oracle GROUP BY clause
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
You cannot use the Oracle WHERE clause to restrict groups
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)