How to SELECT DISTINCT on Multiple Columns in PL/SQL?

PL/SQL language extends SQL by allowing procedural code within Oracle databases. It combines the power of SQL with procedural constructs like loops, conditions, and exception handling. It is a blocked programming language unit that can be named or unnamed blocks. The database does not store unnamed blocks.

In this article, we will see how to count distinct values in a column or a column set with the option to ignore NULL values.

Prerequisites

Before starting with the article, we must know about the COUNT, DISTINCT and GROUP BY functions. The COUNT function returns the count of all values in a column excluding nulls (because null is not a value), the DISTINCT function shows all the distinct combinations of values in a column group and the GROUP BY function is an aggregation that gives all the distinct combinations of values in a column group with the ability to perform operations like SUM, COUNT, etc.

Setting Up Environment

We will create a class table with one of the columns having repeated values (and some NULLs).

CREATE TABLE class(
    name    VARCHAR2(20),
    country VARCHAR2(20)
);

INSERT ALL
    INTO class VALUES('Aayush', 'India')
    INTO class VALUES('Dhruv' , 'India')
    INTO class VALUES('Amy'   , 'Australia')
    INTO class VALUES('Samien', 'Australia')
    INTO class VALUES('Dan'   , 'USA')
    INTO class VALUES('T4PX'  , NULL)
    INTO class VALUES('Alen'  , NULL)
SELECT * FROM DUAL;

SELECT * FROM class;

Output:

Output

Explanation: Here, the Country column is made to have repeated and null values.

Method to SELECT DISTINCT on Multiple Columns in PL/SQL

Method 1: Use COUNT AND DISTINCT (for single column)

We can combine the COUNT and DISTINCT functions to return the count of distinct values in PL/SQL. See the following example:

SELECT COUNT(DISTINCT Country) FROM class;

Output:

Explanation: The DISTINCT command returns four value including NULL but COUNT does not include NULL in its output. That is why we get three as the answer.

We need a separate column to return null counts. See the following example for the same:

SELECT COUNT(DISTINCT Country),
    SUM (CASE WHEN Country IS NULL THEN 1 ELSE 0 END) AS NullCnt
    FROM class;

Output:

Explanation: We get the output as per the above query.

Method 2: Use COUNT AND DISTINCT (for multiple columns)

This method differs from the one above since COUNT in PL/SQL does not allow multiple columns. We will use a subquery to return distinct rows and then count them. See the example below:

SELECT COUNT(*)
FROM (SELECT DISTINCT Country, Name FROM class);

Output:

Explanation: The subquery returns the distinct rows and then we leverage the COUNT(*) command to count only on the required columns.

Method 3: Use GROUP BY Clause

The GROUP BY command allows us to give the values along with the count. See the following example:

SELECT Country, COUNT(*)
    FROM class
    GROUP BY Country;

Output:

Explanation: We get the output as per the above query.

This method runs on multiple columns as well. See the following:

SELECT Country, Name, COUNT(*)
    FROM class
    GROUP BY Country, Name;

Output:

Explanation: We get the output as per the above query.

Conclusion

Overall, In this article we have discussed How to SELECT DISTINCT on multiple columns in PL/SQL along with various methods which are Use COUNT AND DISTINCT (for single column), Use COUNT AND DISTINCT (for multiple columns), and Use GROUP BY along with the output. Now you can easily perform various queries using these methods.