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.
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.