Technical Example

Let’s understand the above methods in this examples in detail manner. Also, create an table and insert some data inside it. The following query creates a sales_record table.

CREATE TABLE sales_record(
    itemId INT,
    itemName VARCHAR2(20),
    qty INT
);

INSERT ALL 
    INTO sale_record VALUES(22, 'Notebook', 12)
    INTO sale_record VALUES(89, 'Pencil', 2)
    INTO sale_record VALUES(22, 'Notebook', 12)
    INTO sale_record VALUES(89, 'Pencil', 2)
    INTO sale_record VALUES(89, 'Pencil', 10)
    INTO sale_record VALUES(66, 'Pen', 56)
    INTO sale_record VALUES(75, 'Geometry Box', 90)
    INTO sale_record VALUES(66, 'Pen', 56)
SELECT * FROM DUAL;

Output:

Explanation: We get the output according to the above query.

As we can see in the table above a lot of records have inserted multiple times. This can cause a lot of issue in production tables when aggregation need to be performed on the table and will lead to inflated values. Now lets count the distinct records in the table. The following query counts all the distinct records in the sales_record table:

SELECT COUNT(*) FROM (
    SELECT DISTINCT * FROM sales_record
);

Output:

If we run the inner subquery.

SELECT DISTINCT * FROM sales_record;

Output:

Explanation: We get the output according to the above query.

PL/SQL is a procedural language designed to allow users to combine the power of procedural language with Oracle SQL. PL/SQL includes procedural language elements such as conditions and loops and can handle exceptions (run-time errors). It also allows the declaration of constants and variables, procedures, functions, packages, types and variables of those types, and triggers.

In this article, we are going to see how we can count distinct values in PL/SQL.