How to use Multiple Subqueries In SQL
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.
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.
Contact Us