Oracle SQL Group by example | How to use SQL group by clause?

In my previous article i have given the details about difference between group by clause and having clause with its examples. In this article we will focus on Oracle SQL Group by example in real world industry scenarios. In this article i would like to focus only on Oracle SQL group by example and how to use SQL group by clause. There is always a confusion while using group by clause. Some of the people also face the error of not a single group by function also while querying with SQL group by clause.

What we will see in this article?

1. What are multiple group functions in oracle?
2. Oracle SQL Group by Example
3. How to use SQL group by clause and related errors.

What are multiple group functions in oracle?

In previous articles we have already given the list of aggregate function in detail. Those functions are also called as group functions. The functions are called as group function because those functions are operating of set of rows and gives one result per group.

The following are the group functions in oracle :

1. Avg
2. Min
3. Max
4. Sum
5. ListAgg
6. STDDEV
7. Variance

The above functions will give the one result for specified group.

Oracle SQL group by example :

In this section we will see the standard example of Oracle group by and some important key points you require to remember at the time of using group by clause in oracle. In real world examples there is need to create the multiple groups and do some calculations on those groups. To create a multiple logical groups inside the query we require to use the oracle group by clause.

You can divide the rows in a table in to smaller groups by using group by clause.

Syntax :

Select Column_names, group_function

from table

where conditional_colum

group by column_name;

Real life scenario :

If we want to calculate the Sum of salary departmentwise for the Employees.

Wrong Query :

Select department_name, Sum(salary) from Employees;

The above statement will give you the error,

ORA-00937: not a single-group group functio

666-00000 – “not a single-group group function”

If this error will occure then you require to use the column_name in group by clause.

Correct Query :

SELECT DEPARTMENT_NAME, SUM(SALARY)
FROM EMPLOYEES
group by DEPARTMENT_NAME ;

The above query is most basic query which will give the sum of salary deparmentwise.

The above are guidelines to use the oracle group clause.

Example 2 : All columns in select should appear in group by clause

SELECT DEPARTMENT_name,job, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_name,JOB order by 1, 2 ;

Example 3 : Creating groups using aliases

User can also create the groups using aliases.

SELECT DEPARTMENT_Name dept_name , SUM(SALARY)
FROM EMPLOYEES
GROUP BY dept_name;

If you can see the dept_name is alias used for department_name column.

Example 4 : You can use the order by clause also.

SELECT DEPARTMENT_name,job, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_name
order by 1, 2 ;

Example 5 : Group by with Having clause

SELECT DEPARTMENT_name, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_name
having SUM(SALARY)>10000
order by DEPARTMENT_name ;

Example 6 : Nested group by

Note that you can use only two nested group by functions in oracle.

SELECT max(sum(salary))
FROM EMPLOYEES
GROUP BY DEPARTMENT_name
order by 1;

These are the ways t use the Oracle SQL Group by clause and oracle SQL group by example as well. These are above some most important examples of oracle sql group by clause. If you like this article or if you have any issues with the same kindly comment in comments section.