Скачать или смотреть How to Efficiently Show Employee Counts from Another Table in Oracle

A quick guide on how to show the number of employees in each department in Oracle while ensuring the count is below two.
---
This video is based on the question https://stackoverflow.com/q/64409857/ asked by the user 'Itati' ( https://stackoverflow.com/u/13424084/ ) and on the answer https://stackoverflow.com/a/64409889/ provided by the user 'Massimo Petrus' ( https://stackoverflow.com/u/6898301/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to show count from another table?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Problem

If you're new to Oracle, you might find yourself needing to aggregate data across different tables. A common scenario is counting the number of employees in each department while filtering the results based on certain criteria. In this case, the requirement is to display the department_id and department_name, alongside a count of employees working in that department, with the restriction that the count must be less than 2.

Your Starting Point

Your initial SQL query looked like this:

[[See Video to Reveal this Text or Code Snippet]]

However, while this query is functional for filtering, it does not include the employee count in the output, which is a crucial part of your requirement.

The Solution: Enhancing Your SQL Query

To address your need for displaying the employee count along with each department, you can make some adjustments to your SQL. Below, I’ll outline two effective approaches to achieve this.

Approach 1: Using a Left Join

This approach uses a LEFT JOIN to combine data from the DEPARTMENTS table and a sub-query that counts employees per department.

Step-by-step Breakdown:

Select the needed fields: You’ll need DEPARTMENT_ID, DEPARTMENT_NAME, and the employee count.

Left join the employee counts: Aggregate counts of employees for each department.

Include handling for departments without employees: Use NVL (or equivalent) to handle cases where there are no employees.

Here’s the improved SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

NVL(e2.cnt, 0): This function ensures that if a department has no employees, it will return a count of 0 instead of NULL.

Approach 2: More Compact Query

If you're looking for a more concise way to achieve the same result, this alternative uses the GROUP BY clause effectively.

SQL Query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

The HAVING clause filters the groups based on the count, ensuring that you only get departments with fewer than two employees.

Conclusion

By using these SQL queries, you can efficiently display the counts of employees in various departments, making your reports and analyses much more informative. Whether you prefer a more detailed breakdown or a more compact form, both approaches meet the requirement specified in your original problem.

Keep experimenting with Oracle SQL, and don’t hesitate to reach out if you have more questions!