How to use Conditional Aggregation In SQL
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.
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