How to Count Distinct Values in PL/SQL?
Last Updated : 07 Nov, 2024
PL/SQL, an extension of SQL for Oracle databases, allows developers to blend procedural constructs like conditions and loops with the power of SQL. It supports exception handling for runtime errors and enables the declaration of variables, constants, procedures, functions, packages, triggers, and more. One common task in PL/SQL is counting distinct values from a table—a useful operation for data aggregation and analysis.
In this article, we are going to see how we can count distinct values in PL/SQL.
Setting Up The Environment
Let's create a sample table and insert some records in it.
PL/SQL 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:
Using the 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: This query returns unique values from the val1 column by filtering out duplicates.
Using 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:
function.png)
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: The inner query retrieves unique records from the sample table, and the outer query counts these distinct records.
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: This query groups records by val1, val2, and val3 to make them unique before counting.
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.
PL/SQL 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.
Counting Distinct Records
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.
Similar Reads
How to Count Distinct Values in MySQL?
The COUNT DISTINCT function is used to count the number of unique rows in specified columns. In simple words, this method is used to count distinct or unique values in a particular column. In this article, we are going to learn how we can use the Count Distinct function in different types of scenari
5 min read
How to find distinct values of multiple columns in PySpark ?
In this article, we will discuss how to find distinct values of multiple columns in PySpark dataframe. Let's create a sample dataframe for demonstration: [GFGTABS] Python3 # importing module import pyspark # importing sparksession from pyspark.sql module from pyspark.sql import SparkSession # creati
2 min read
How to Count Unique and Distinct Values in Excel
Counting unique values in Excel is a common challenge when working with large datasets, especially for those analyzing data or generating reports. Knowing how to count distinct Excel values accurately is crucial when managing data like customer lists, product inventories, or sales records. This arti
15 min read
How to Plot Value Counts in Pandas
In this article, we'll learn how to plot value counts using provide, which can help us quickly understand the frequency distribution of values in a dataset. Table of Content Concepts Related to Plotting Value CountsSteps to Plot Value Counts in Pandas1. Install Required Libraries2. Import Required L
3 min read
How to SELECT DISTINCT on Multiple Columns in SQL?
In the world of databases, data duplication can lead to confusion and inefficiency. SQL provides a powerful tool, SELECT DISTINCT, to retrieve unique values from columns. However, when dealing with multiple columns, the approach becomes more detailed. In this article, we will explain how to use SELE
4 min read
How to Find Duplicates Values Across Multiple Columns in SQL?
In SQL, identifying duplicate entries across multiple columns is crucial for ensuring data integrity and quality. Whether we're working with large datasets or trying to clean up existing data, we can efficiently find duplicates using GROUP BY and COUNT() functions. In this article, we'll focus on ho
3 min read
How to Declare a Variable in PL/SQL?
Declaring variables in PL/SQL is a fundamental step towards building powerful and efficient database applications. Variables act as placeholders for data which enable us to manipulate and store information within our PL/SQL programs. Here, we will explore various methods of declaring variables in PL
5 min read
How to Get the Top 10 Values in PL/SQL?
PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features. PL/SQL supports SQL queries. PL/SQL contains a declaration section, execution section, and exception-handling section. Declare and exception handling sections are optional. This article exp
6 min read
How to Specify Condition in Count() in PL/SQL?
In the domain of database management and querying, PL/SQL (Procedural Language/Structured Query Language) stands as a powerful tool. It empowers the developers and database administrators to manipulate the data and implement business logic directly within the Oracle Database. The common task in data
4 min read
How to Use Count With Condition in PostgreSQL?
In PostgreSQL, the COUNT() function serves as a tool for tallying the number of records within a table. This article aims to address this query, delving into the nuances and implications of integrating conditions into the COUNT() function in PostgreSQL. The COUNT() function in PostgreSQL is traditio
4 min read