How to Resolve ORA-00979 and ORA-00937 Errors in Oracle SQL

Опубликовано: 08 Октябрь 2024
на канале: blogize

Summary: Learn how to troubleshoot and fix ORA-00979 and ORA-00937 errors in Oracle SQL when dealing with "not a Group By expression" issues.
---

How to Resolve ORA-00979 and ORA-00937 Errors in Oracle SQL

If you've ever worked with Oracle SQL, chances are you've encountered errors like ORA-00979 and ORA-00937. These can be stumbling blocks, but understanding their root causes can lead to quicker and more effective fixes.

Understanding ORA-00979 and ORA-00937

ORA-00979: Not a GROUP BY Expression
The ORA-00979 error occurs when you execute a SELECT statement with a GROUP BY clause that fails to include all non-aggregate columns in the SELECT list.

Example:

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

This will throw an ORA-00979 error because employee_id is not part of the GROUP BY clause.

ORA-00937: Not a Single-Group Group Function
The ORA-00937 error is thrown when you use an aggregate function like COUNT, SUM, or AVG without grouping the non-aggregate columns.

Example:

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

This will throw an ORA-00937 error because the query isn't specifying how to group department_id values.

How to Resolve These Errors

Resolve ORA-00979

To resolve ORA-00979, ensure that all columns in the SELECT list that are not aggregates are included in the GROUP BY clause.

Corrected Example:

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

In this corrected query, both department_id and employee_id are included in the GROUP BY clause, eliminating the error.

Resolve ORA-00937

To resolve ORA-00937, include a GROUP BY clause to specify how the rows should be grouped.

Corrected Example:

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

Here, by grouping by department_id, you will have a valid query with no ORA-00937 error.

Additional Tips

Check Query Structure: Always double-check your SELECT statement and GROUP BY clause to make sure they align.

Use Aliases: Use column aliases to make your code cleaner and reduce errors due to ambiguous column references.

Review Execution Plan: Utilize Oracle's execution plans to troubleshoot and optimize queries, which can help in identifying incorrect GROUP BY usage.

Conclusion

Errors like ORA-00979 and ORA-00937 can seem daunting at first, but with a solid understanding of how GROUP BY works in SQL, you can quickly overcome these challenges. Always ensure that non-aggregate columns in the SELECT clause are included in the GROUP BY clause, and use aggregate functions appropriately to avoid these Oracle SQL errors.

By keeping these guidelines in mind, you'll be well-equipped to handle and resolve these common SQL issues effectively.