How to Get Multiple Counts With Single Query in PL/SQL?

In PL/SQL, it’s very common that we need to count rows based on the different conditions in the single query. This can be done using conditional aggregation or we also do this with the multiple subqueries within the SELECT statement.

Here, the SELECT statement is necessary to perform this operation. In this article, we will explore both approaches i.e. Conditional aggregation and Multiple subqueries along with examples and their explanations.

How to Obtain Multiple Counts With a Single Query in PL/SQ

To obtain multiple counts with a single query in PL/SQL, you can use either conditional aggregation or multiple subqueries. Conditional aggregation involves using the SUM() function with CASE statements to count rows based on specific conditions. Multiple subqueries employ separate SELECT statements within the main query to count rows for each condition. Each approach has its advantages and is suited to different requirements.

• “Using Conditional aggregation” involves using the SUM() function with conditional expressions to count rows based on specific conditions.
• “Using Multiple subqueries“, uses separate SELECT statements to count rows for each condition.

Let’s Setup an Environment

Let’s create an ”employeeDetails” and insert the value in it:

-- Create table
CREATE TABLE employeeDetails (
    id NUMBER,
    department VARCHAR2(50),
    salary NUMBER
);

-- Insert sample data
INSERT INTO employeeDetails (id, department, salary) VALUES (1, 'HR', 55000);
INSERT INTO employeeDetails (id, department, salary) VALUES (2, 'IT', 60000);
INSERT INTO employeeDetails (id, department, salary) VALUES (3, 'HR', 50000);
INSERT INTO employeeDetails (id, department, salary) VALUES (4, 'IT', 45000);
INSERT INTO employeeDetails (id, department, salary) VALUES (5, 'Finance', 70000);
INSERT INTO employeeDetails (id, department, salary) VALUES (6, 'Finance', 48000);
INSERT INTO employeeDetails (id, department, salary) VALUES (7, 'HR', 52000);

Before moving to perform both operations let’s first create a table Named “employeeDetails” and insert values into it.

Create Table and Insert Values

1. Using Conditional Aggregation

The SUM() function is used with conditional expressions so that we count rows based on different-different conditions. Now the CASE statement evaluate each row against the specified condition, add 1 to the count if true otherwise 0 if false.

Syntax:

SELECT 
    column1,
    SUM(CASE WHEN condition1 THEN 1 ELSE 0 END) AS count1,
    SUM(CASE WHEN condition2 THEN 1 ELSE 0 END) AS count2
FROM table_name
GROUP BY column1;

Example: Employee Salary Distribution by Department

Query:

-- Query using conditional aggregation
SELECT 
    department,
    SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_salary_count,
    SUM(CASE WHEN salary <= 50000 THEN 1 ELSE 0 END) AS low_salary_count
FROM employeedetails
GROUP BY department;

Output:

Explanation: This query aggregates data from the “employeedetails” table, categorizing employees by department. For each department, it counts the number of employees with salaries above and below 50000, labeling them as “high_salary_count” and “low_salary_count” respectively, providing insights into salary distribution within departments.

2. Using Multiple Subqueries

Using multiple subqueries in PL/SQL enables counting rows based on different conditions within a single query. Each subquery retrieves counts for specific conditions, allowing for a comprehensive analysis of the data. This approach provides flexibility in counting rows based on various criteria without needing multiple separate queries.

Syntax:

SELECT 
    column1,
    (SELECT COUNT(*) FROM table_name WHERE condition1) AS count1,
    (SELECT COUNT(*) FROM table_name WHERE condition2) AS count2
FROM dual;

Example: Count of High and Low Salary Employees by Department

-- Query using multiple subqueries
SELECT 
    department,
    (SELECT COUNT(*) FROM employeedetails WHERE department = e.department AND salary > 50000) AS high_salary_count,
    (SELECT COUNT(*) FROM employeedetails WHERE department = e.department AND salary <= 50000) AS low_salary_count
FROM (SELECT DISTINCT department FROM employeedetails) e;

Output:

Output: Multiple Subqueries

Explanation: This query categorizes employees by department, using subqueries to count those with salaries above and below 50000 for each department. It provides department-wise counts of high and low-salary employees, ensuring distinct departmental representation.

Conclusion

In conclusion, both conditional aggregation and multiple subqueries offer effective ways to obtain multiple counts with a single query in PL/SQL. While conditional aggregation simplifies the process with concise syntax, multiple subqueries provide flexibility for complex counting scenarios. Choosing the method depends on the specific requirements of the analysis.