How to Resolve ORA-00979 Errors Caused by GROUP BY and ORDER BY Clause Conflicts in SQL?

Troubleshooting ORA-00979: GROUP BY and ORDER BY Clause Conflicts

The ORA-00979 error in SQL arises from a mismatch between the GROUP BY and ORDER BY clauses. GROUP BY aggregates data, while ORDER BY sorts it. The error occurs when ORDER BY includes columns not present in GROUP BY.

Understanding the Problem

Consider this example query that produces the ORA-00979 error:

<code class="language-sql">SELECT cr.review_sk, cr.cs_sk, cr.full_name,
       LISTAGG(TO_CHAR(cf.fact_date, 'mm/dd/yyyy'), ',') WITHIN GROUP (ORDER BY cf.fact_date) AS "appt",
       cs.cs_id, cr.tracking_number
FROM review cr JOIN cs ON cr.cs_sk = cs.cs_sk
JOIN fact cf ON cr.review_sk = cf.review_wk
WHERE UPPER(cs.cs_id) LIKE '%' || UPPER(i_cs_id) || '%'
  AND row_delete_date_time IS NULL
  AND cr.fact_type_code = 183050
GROUP BY cr.review_sk, cr.cs_sk, cr.tracking_number
ORDER BY cs.cs_id, cr.full_name;</code>

The error stems from ORDER BY cs.cs_id, cr.full_name. cs.cs_id and cr.full_name are not in the GROUP BY clause.

Solutions

To resolve ORA-00979, all columns in ORDER BY must either be in GROUP BY or be aggregated:

Solution 1: Add to GROUP BY

If the columns are essential for grouping, include them in the GROUP BY clause:

<code class="language-sql">GROUP BY cr.review_sk, cr.cs_sk, cr.tracking_number, cs.cs_id, cr.full_name
ORDER BY cs.cs_id, cr.full_name;</code>

Solution 2: Use Aggregate Functions

If the columns are not crucial for grouping, use aggregate functions like MIN, MAX, or others appropriate to your data:

<code class="language-sql">GROUP BY cr.review_sk, cr.cs_sk, cr.tracking_number
ORDER BY MIN(cs.cs_id), MIN(cr.full_name);</code>

Choosing the correct solution depends on your query's logic and desired results. Adding to GROUP BY maintains more detail, while using aggregate functions summarizes data within groups. Carefully consider the implications of each approach before implementing it. Remember to use appropriate aggregate functions based on your data and desired outcome.

The above is the detailed content of How to Resolve ORA-00979 Errors Caused by GROUP BY and ORDER BY Clause Conflicts in SQL?. For more information, please follow other related articles on the PHP Chinese website!