Why Does My SQL Query Result in an ORA-00979 Error, and How Can I Fix It?

Troubleshooting ORA-00979: A Common SQL GROUP BY Issue

The dreaded ORA-00979 error often surfaces when working with SQL's GROUP BY clause. This error signifies a mismatch between the columns selected and those grouped. Specifically, it arises when SELECT statements include columns not present in the GROUP BY clause, or when non-aggregate functions are applied to ungrouped columns.

Let's examine a problematic query:

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

This query triggers ORA-00979 because the GROUP BY clause is incomplete. To rectify this, either include all non-aggregated columns from the SELECT list in the GROUP BY clause, or apply aggregate functions (like COUNT, SUM, MIN, MAX, AVG) to those columns not explicitly grouped.

Here's a corrected version:

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

The key change is adding cr.full_name and cs.cs_id to the GROUP BY clause. Alternatively, if you only need aggregated results for cr.full_name and cs.cs_id, appropriate aggregate functions should be used in the SELECT statement. By ensuring that every column in the SELECT list is either aggregated or included in the GROUP BY clause, the ORA-00979 error is eliminated. The removal of tolist() function also helps resolve the issue as it's not an aggregate function.

The above is the detailed content of Why Does My SQL Query Result in an ORA-00979 Error, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!