PL/SQL GROUP BY Clause

The GROUP BY clause in PL/SQL is a powerful tool used to organize data into aggregated groups based on one or more columns. It is essential for performing summary operations on large datasets, enabling efficient data analysis by grouping rows that share common values.

In this article, We will learn about PL/SQL GROUP BY Clause with the help of various examples and so on in detail.

PL/SQL GROUP BY Clause

• The GROUP BY clause in PL/SQL is used to group rows with the same values in specified columns into aggregated data.
  
• This clause is commonly used in SQL queries to aggregate data, which means summarizing or performing calculations on sets of rows.
  
• When using GROUP BY, we typically pair it with aggregate functions like SUM(), COUNT(), AVG(), MAX(), and MIN() to compute summary statistics or perform other calculations on grouped data

Syntax:

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

Parameters:

1. expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.
   
2. aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.
   
3. aggregate_expression: It specifies the column or expression on that the aggregate function is based on.
   
4. tables: It specifies the table from where you want to retrieve records.
   
5. conditions: It specifies the conditions that must be fulfilled for the record to be selected.

Examples of PL/SQL GROUP BY Clause

To illustrate how the GROUP BY clause works in PL/SQL, we first need to create a table and insert some sample data. This setup will help us demonstrate various operations using GROUP BY to avoid redundant creation.

We'll start by creating a table named student with columns for name, course_fee, and course. This table will hold information about students, the fees they pay, and the courses they are enrolled in.

Create Student Table

Let’s assume that we have Student Sample Table as follows, we will do some specific operations to learn about GROUP BY Clause in PL/SQL. Next, we'll insert some example records into the student table. Each record represents a student, their course fee, and the course they are taking. Here’s the sample data we’ll use:

CREATE TABLE student (
  name VARCHAR(50),
  course_fee DECIMAL(10,2),
  course VARCHAR(50)
);

Insert Sample Data

INSERT INTO student (name, course_fee, course) VALUES
('Aleena', 5000.00, 'Mathematics'),
('Ben', 6000.50, 'English'),
('Chris', 7500.75, 'Science'),
('Darla', 4500.25, 'Mathematics'),
('Erie', 8000.00, 'English'),
('Monty', 6500.00, 'Science'),
('Aleena', 5500.50, 'Mathematics'),
('Chris', 7200.75, 'Science'),
('Darla', 4800.60, 'Mathematics'),
('Ben', 8300.00, 'English');

Output:

Example 1: Using SUM Function

Let's look at an PL/SQL GROUP BY query example that uses the SUM function. This PL/SQL GROUP BY example uses the SUM function to return the name of the student and the total course_fee (of the student).

The query groups the data by the name column, meaning it combines all records with the same student name. For each student, it then calculates the total of their course_fee values using the SUM function. The result is a list of student names along with the total fees they have paid.

Query:

SELECT name, SUM(course_fee) FROM student 
GROUP BY name;

Output:

Explanation: As we can see in the above output, the rows with duplicate NAMEs are grouped under the same NAME and their corresponding COURSE_FEE is the sum of the COURSE_FEE of duplicate rows. The SUM() function of PL/SQL is used here to calculate the sum. The NAMES that are added are Aleena, Chris, Darla, Ben.

Example 2: Using Count Function

The query groups the records in the Student table by course and name and then counts the number of occurrences (rows) for each combination of course and name. The COUNT(*) function counts the number of rows for each unique combination of name and course.

In this example, we use the COUNT function with the GROUP BY clause to count the number of records for each combination of course and name. This allows you to see how many times each student appears in the table for each course.

Query:

SELECT Name, course, Count(*)
FROM Student
GROUP BY Course, Name;

Output:

Explanation:

The output shows each student’s name, the course they are enrolled in, and the count of how many times that student appears in the data for that course. This helps identify how many records there are for each student-course combination.

Example 3: Using MIN Function

Let's next look at how we could use the GROUP BY clause with the MIN function. The MIN(course_fee) function calculates the lowest course fee for each student within their respective group. Whereas in this example GROUP BY uses the MIN function to return the name of each Student and the minimum course_fee of the student.

Query:

SELECT Name, MIN(course_fee) AS "Lowest course_fee"
FROM student
GROUP BY name;

Output:

Explanation:

The output displays each student's name and the smallest course fee they have been charged. For example, if a student has multiple records with different fees, this query shows the lowest fee they paid. This helps to quickly identify the minimum fee each student has encountered.

Example 4: Using MAX Function

The GROUP BY clause groups the records by the course column. This means the query processes the data for each unique course separately. The MAX(course_fee) function calculates the highest course fee recorded for each course group.

It identifies the largest fee any student has paid for each course. Hence, Using the MAX function to find the maximum course_fee for each student grouped by the course. This query as an output returns the course and the highest course_fee recorded in that subject.

Query:

SELECT course, MAX(course_fee) AS "Highest course_fee"
FROM student
GROUP BY course;

Output:

Explanation:

The output shows the maximum course fee for each course offered. For each course, it highlights the highest fee that any student has paid. For example, if the highest fee for the 'Mathematics' course is $5500.50, this value will be shown next to the course name. This helps to see the maximum expenditure for each course and understand which course has the highest fee paid by any student.

Key Features

• Order of Execution: The GROUP BY clause is placed after the WHERE clause and before the ORDER BY and HAVING clauses in a SQL query.
  
• Data Aggregation: The GROUP BY clause groups rows that have the same values in specified columns, enabling the use of aggregate functions like SUM, COUNT, AVG, MIN, and MAX to calculate summary values for each group.
  
• Multiple Columns Grouping: You can group data by one or more columns, allowing for more detailed and granular analysis.
  
• HAVING Clause Compatibility: Works with the HAVING clause, which is used to filter groups based on aggregate functions or conditions.
  
• NULL Value Grouping: All NULL values in a column are treated as a single group.
  
• Performance Optimization: Helps optimize queries when dealing with large datasets by reducing the number of rows processed through grouping.
  
• Subqueries: Can be used in subqueries for complex data analysis, allowing for nested grouping and aggregation.
  
• Result Order: The GROUP BY clause does not inherently order the results. If you need ordered results, you must use the ORDER BY clause.

Conclusion

The GROUP BY clause in PL/SQL is essential for organizing and summarizing data within a database. Through its ability to group records based on specified columns, it enables efficient aggregation of data, allowing us to compute totals, counts, minimums, and maximums across various categories.

In the context of the student table, the GROUP BY clause was instrumental in revealing insights such as the total course_fee per student, the highest and lowest course_fee in each course, and the frequency of occurrences within different categories.