Skip to main content

Aggregate Functions and Group By Clause in Oracle with Examples #Diksha Oracle Training Centre

 Aggregate Functions and Group By Clause Oracle with Examples.

 

Hello Everyone,

Today I will  discuss about  Aggregate function in SQL by using Group By Clause and different clauses with some examples . Please go tjrough my previous blogs in Archive folder for classification of SQL, Commands and  SQL joins for your understanding.

 Aggregate Functions Allows us to perform a calculation on a set of values to return a single value . We can use Group by Clause to group the result-set by one or more columns. Also we can use Having clause to restrict or filter the data as per our requirement.

Note: Whenever we use Aggregate function in SQL we can’t able to use where condition. To restrict or filter the record we need to use having clause instead of Where.

Below is the most commonly used Aggregate function in SQL.

 

MAX : Max function  used to get the maximum values in a set of values.

COUNT : This function used to count rows in specified tables.

AVG   : This function is used to calculate the average of set values.

SUM  : This Function is used to calculate the sum of Values.

MIN : MIN Function  used to get the minimum values in a set of values.

Apart from that many Aggregate functions available in Oracle database like STDDEV,VARIANCE, RANK etc . But most commonly used aggregate functions are MAX, MIN, AVG,COUNT and SUM .

 

SQL Aggregate Functions Examples :

Let’s understand with some examples of using SQL aggregate functions:

Suppose I have one table Employee if i want to see the max sal of employees then we use below syntax:

SELECT MAX(SALARY)  AS MAXIMUM_SALARY

FROM EMPLOYEE;

Max function  used to get the maximum values in a set of values , but it will only give one values.

Above query will give only 1 result as we know function must return a value but only single value, suppose if I want to see the maximum salary of employee for each employee we can use Group by Clause to  get the Maximum Salary for each employee.

 

Below is the command for this to get maximum salary by  dept_no

select max(salary)

 as Maximum_salary,

 emp_id,

 empname

 from employee

 group by emp_id, empname;

 

Below is the  output of both query.

 

 


 

 


 

To filter or restrict the data in aggregate function we can use Having instead of where condition.

Let’s take one example: If I want to see the max salary greater than 40000 for each employee id , then we can use below SQL command:

 

select max(salary),

 emp_id,

 empname

 from employee

 group by emp_id,empname

 having max(salary) > 40000;

Below is the image output for your reference of above query.


 

 

Here we see the uses of group by and having clause uses in Aggregate Function.

We can use other Function in same Manner , let’s have use of SUM function and other functions you can use in the same way what I have written.

 Select Sum(salary) as sum_salary ,

empname

From employee ;

Below is the image output for your reference of above query.



Here we used function so it returns only 1 row that is sum value of salary.

 

If I want to get the sum of salary employee id wise then we can use below query by using group by clause:

select sum(salary) as sum_salary,

emp_name,

emp_id

 from employee

group by emp_name ,emp_id;

 

Below is  the  output  of above query.

 


 

If I want to restrict the data as I want to see the sum of salary is greater than 40000 below is the query we can use to restrict the data by having clause:

 

select sum(salary) ,

empname,

emp_id

 from employee

 group by empname,emp_id

having sum_salary >40000;

 

Below is the output of above query.

 


I have given examples of 2 aggregate functions with Group by and having clause. Please try to use different functions like MIN , COUNT,MAX, AVG , STDDEV VARIANCE etc in same way as per your requirement . Design and create table and put some logical data and do the analysis as per your need. You can follow my previous blogs for  creating  and inserting the data in tables by using DDL and DML .

 

You can use Functions  in Subquery , Joins and once I started PL/SQL blogs this functions can be very useful.

 

For now I just right one Sub query for your reference and please you can also try to write aggregate query using group by clause in different scenario.

 

Below is the Sub query :

select max(salary),

emp_id,

EMPNAME

 from employee

where emp_id in

 (select emp_id

 from department

  where DEPT_NAME='IT')

group by emp_id,EMPNAME

 

Below is the image output for your reference of above query.



Here we not use having clause because  as in Inner query we use simple select query to filter the data.

Note: Sub query Part I will cover in my next blog with more details   

For now I suggest you to design/Create 2 or 3 tables with logical data and use aggregate functions with group by having clause and write some meaningful queries as you can take reference of Join from my previous blogs.

 

Group by and Aggregate functions mostly used in companies to generate report and do some trend analysis.

 

 Please go through this blog Carefully and try to use all the aggregate functions and let me know if you any issues. This blog is important for fresher’s who is preparing for interview as SQL developer.

 

In next Blog I will come with Oracle sub queries and Co-related Sub queries.

 

Thanks

Comments

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li