Examples of DISTINCT and GROUP BY Clause

To understand DISTINCT and GROUP BY Clause in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a table called employees which contains employee_id, employee_name, salary, age and department_name as Columns.

Query:

-- Create table with new fields
CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(50),
    department_name VARCHAR(50),
    salary DECIMAL(10, 2),
    age INT
);

-- Insert values into the table
INSERT INTO employees (employee_id, employee_name, department_name, salary, age)
SELECT 1, 'John', 'Sales', 62000, 30 FROM DUAL
UNION ALL
SELECT 2, 'Alice', 'IT', 68000, 35 FROM DUAL
UNION ALL
SELECT 3, 'Bob', 'Sales', 65000, 28 FROM DUAL
UNION ALL
SELECT 4, 'Emma', 'HR', 48000, 40 FROM DUAL
UNION ALL
SELECT 5, 'Alice', 'Marketing', 62000, 32 FROM DUAL
UNION ALL
SELECT 6, 'Jane', 'IT', 65000, 29 FROM DUAL;

-- Show table
SELECT * FROM EMPLOYEES;

After Inserting some records into the employees, the table looks:

Example of DISTINCT Clause

Example 1: Find Distinct Department Names

Suppose we have a table named employees with duplicate entries. To fetch unique department names, we use the DISTINCT clause.

Query:

SELECT DISTINCT department_name
FROM employees;

Output:

Explanation: This query retrieves unique department names from the employees table. Each department name appears only once in the output.

Example 2: Find Distinct employee Names

Query:

SELECT DISTINCT employee_name
FROM employees;

Output:

Explanation: This query retrieves unique employee names from the employees table. Each employee name appears only once in the output.

Example of GROUP BY Clause

Example 1: Calculating Sum of Salaries of employees According to Deparment

Calculating the sum of salaries of employees of each department using SUM() function.

Query:

SELECT department_name, SUM(salary) AS total_salary
FROM employees
GROUP BY department_name;

Output:

Explanation: This query calculates the total salary for each department by summing up the salaries of all employees within each department.

Example 2: Calculating Average Age of employees According to Department

Calculating the average age of employees of each department using AVG() function.

Query:

SELECT department_name, AVG(age) AS avg_age
FROM employees
GROUP BY department_name;

Output:

Explanation: This query calculates the average age of employees in each department by averaging the ages of all employees within each department.

In PL/SQL, knowing the difference between DISTINCT and GROUP BY is important for working with data effectively. Although DISTINCT and GROUP BY might seem similar, they serve different purposes. In this article, we’ll explore DISTINCT and GROUP BY Clause with the syntax and various examples along the difference between them for better understanding.