This Oracle tutorial explains how to use the Oracle GROUP BY clause with syntax and examples.
The Oracle GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The syntax for the GROUP BY clause in Oracle/PLSQL is:
SELECT expression1, expression2, ... expression_n,
GROUP BY expression1, expression2, ... expression_n;
Parameters or Arguments
expression1, expression2, ... expression_n
The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.
It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
This is the column or expression that the aggregate_function will be used on.
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
Optional. The conditions that must be met for the records to be selected.
Example - Using SUM function
Let's look at an Oracle GROUP BY query example that uses the SUM function.
This Oracle GROUP BY example uses the SUM function to return the name of the product and the total sales (for the product).
SELECT product, SUM(sale) AS "Total sales"
GROUP BY product;
Because you have listed one column (the product field) in your SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY clause. The product field must, therefore, be listed in the GROUP BY clause.
Example - Using COUNT function
Let's look at how we could use the GROUP BY clause with the COUNT function.
This GROUP BY example uses the COUNT function to return the category and the number of suppliers (in that category) that have over 45 available_products.
SELECT category, COUNT(*) AS "Number of suppliers"
WHERE available_products > 45
GROUP BY category;
Example - Using MIN function
Let's next look at how we could use the GROUP BY clause with the MIN function.
This GROUP BY example uses the MIN function to return the name of each department and the minimum salary in the department.
SELECT department, MIN(salary) AS "Lowest salary"
GROUP BY department;
Example - Using MAX function
Finally, let's look at how we could use the GROUP BY clause with the MAX function.
This GROUP BY example uses the MAX function to return the name of each department and the maximum salary in the department.
SELECT department, MAX(salary) AS "Highest salary"
GROUP BY department;