How to Count Distinct Values in PL/SQL?

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.

Setting Up Environment

Let’s create a sample table and insert some records in it.

CREATE TABLE sample(
    val1 INT,
    val2 INT,
    val3 INT
);

INSERT ALL 
    INTO sample VALUES(23, 34, 56)
    INTO sample VALUES(99, 29, 12)
    INTO sample VALUES(73, 11, 90)
    INTO sample VALUES(23, 34, 56)
    INTO sample VALUES(11, 73, 33)
    INTO sample VALUES(23, 34, 56)
SELECT * FROM DUAL;

Output:

DISTINCT Keyword in PL/SQL

The DISTINCT keyword is a keyword which is used to fetch unique or distinct records from a table.

Syntax:

SELECT DISTINCT expression1, expression2, ... expression_n
FROM table
[WHERE conditions]

Explanation:

• expression1, expression2, … expression_n: These are expressions that will be used to de-duplicate records
• table: The table from which to perform distinct operation.
• conditions: The condition on which to filter the table optionally.

Example:

The following query retrieves all the distinct values in the val1 column:

SELECT DISTINCT(val1) FROM sample;

Output:

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

COUNT() function in PL/SQL

The COUNT() function is used to count the non-null records from a table

Syntax:

SELECT [expression1, expression2, ... expression_n,]
       COUNT(aggregate_expression)
FROM table
[WHERE conditions]
[GROUP BY expression1, expression2, ... expression_n];

Explanation:

• expression1, expression2, … expression_n: These are expressions that might be included in the GROUP BY clause
• aggregate_expression: The expression whose non-null values are to be counted.
• table: The table from which to count.
• conditions: The condition on which to filter the table optionally.

Example:

The following query counts the number of records in the table:

SELECT COUNT(*) FROM sample;

Output:

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

GROUP BY Clause in PL/SQL

The GROUP BY clause is used to collect data from various records by group them using one or more columns.

Syntax:

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

Explanation:

• expression1, expression2, … expression_n: These are expressions that are included in the GROUP BY clause
• aggregate_function: The function which will be used to aggregate the records.
• aggregate_expression: The expression whose values are to be aggregated.
• table: The table from which to count.
• conditions: The condition on which to filter the table optionally.

Example:

The following query sums the values of val2 field grouped by val1 field:

SELECT val1, SUM(val2) AS sum FROM sample
GROUP BY val1;

Output:

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

Method to Count Distinct Values

Method 1: Using DISTINCT Keyword

We can make use of the DISTINCT keyword to count the distinct values in the table. In the following query we have made use of subquery to first retrieve the distinct records from the table and later used that along with count() function to get the distinct count:

SELECT COUNT(*) AS distinct_cnt FROM (
     SELECT DISTINCT * FROM sample   
);

Output:

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

Method 2: Using GROUP BY Clause

We can make use of the GROUP BY clause to group all the duplicate values into one record. In the following query we have made the use of subquery to first group all the records to make them unique and later used them to get the distinct count:

SELECT COUNT(*) AS distinct_cnt FROM (
     SELECT val1, val2, val3 FROM sample 
    GROUP BY val1, val2, val3
);

Output:

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

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.

Conclusion

Overall, after reading the whole article now you have good understanding about how to count distinct values through various methods like Using DISTINCT and Using GROUP BY method. In this article we have implemented various method and saw the example along with the output and their explanations. Now you can easily use these method and insert records into the tables easily.