Using GROUP BY Clause in Oracle

This article is written about how to use the Oracle GROUP BY clause with syntax and examples.

Description

The Oracle GROUP BY clause is used in a SELECT announcement to gather facts throughout a couple of files and group the outcomes through one or extra columns.

Syntax

The syntax for the GROUP BY clause in Oracle/PLSQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, … expression_n The expressions that are no longer encapsulated inside an combination function and have to be included in the GROUP BY clause. aggregate_function It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions. aggregate_expression This is the column or expression that the aggregate_function will be used on. tables The tables that you wish to retrieve information from. There have to be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that have to be met for the archives to be selected.

Example – Using SUM function

Let’s look at an Oracle GROUP BY question example that makes use of the SUM function.

This Oracle GROUP BY instance uses the SUM characteristic to return the name of the product and the total income (for the product).

SELECT product, SUM(sale) AS "Total sales"
FROM order_details
GROUP BY product;

Because you have listed one column (the product field) in your SELECT declaration that is no longer encapsulated in the SUM function, you have to use the GROUP BY clause. The product discipline must, therefore, be listed in the GROUP BY clause.

Example – Using COUNT function

Let’s seem to be at how we ought to use the GROUP BY clause with the COUNT function.

This GROUP BY instance makes use of the COUNT feature to return the class and the wide variety of suppliers (in that category) that have over forty five available_products.

SELECT category, COUNT(*) AS "Number of suppliers"
FROM suppliers
WHERE available_products > 45
GROUP BY category;

Example – Using MIN function

Let’s next look at how we should use the GROUP BY clause with the MIN function.

This GROUP BY example makes use of the MIN feature to return the title of each branch and the minimum profits in the department.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

Example – Using MAX function

Finally, let’s seem at how we may want to use the GROUP BY clause with the MAX function.

This GROUP BY example uses the MAX characteristic to return the name of every branch and the most revenue in the department.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;