How can Oracle group data for comparison within the same group?

2 years ago
William Carter
1 minute

In Oracle, you can use the GROUP BY clause to group data. After grouping, you can use the HAVING clause to compare within the groups.

Here is an example query showing how to group the employee table by department and compare the average salary for each department.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

In the above query, first we group by department using the GROUP BY clause, then calculate the average salary for each department. Finally, we use the HAVING clause to filter out departments with an average salary greater than 5000.

Please note that if you want to make comparisons within groups, you must use the HAVING clause instead of the WHERE clause. This is because the WHERE clause filters data before grouping, while the HAVING clause filters data after grouping.

bannerAds