Why Does My SQL Query Generate ORA-00979: Not a GROUP BY Expression?

Decoding the ORA-00979 Error in SQL's GROUP BY Clause

This common SQL error, ORA-00979 ("Not a GROUP BY expression"), occurs when your query uses GROUP BY but includes columns in the SELECT list that aren't either grouped or aggregated. Let's explore how to fix this.

The core problem lies in the mismatch between the SELECT and GROUP BY clauses. GROUP BY consolidates rows with matching values into groups. Every column in your SELECT statement must either:

1. Appear in the GROUP BY clause: This ensures each selected value is directly associated with a group.
2. Be aggregated: Use functions like MIN(), MAX(), SUM(), AVG(), COUNT() to summarize values within each group.

Illustrative Example

Imagine a table with appointments:

Appointment ID (appt_id)	Patient Name (patient)	Doctor (doctor)
1	Alice	Dr. Smith
2	Bob	Dr. Smith
3	Charlie	Dr. Jones

An incorrect query:

<code class="language-sql">SELECT appt_id, patient, doctor FROM appointments GROUP BY doctor;</code>

This will fail with ORA-00979 because appt_id and patient are not grouped.

Correct Approaches

• Include all non-aggregated columns in GROUP BY:

<code class="language-sql">SELECT doctor, COUNT(*) FROM appointments GROUP BY doctor;</code>

This counts appointments per doctor.

• Use aggregate functions:

<code class="language-sql">SELECT doctor, COUNT(*) AS appointment_count, MIN(appt_id) AS first_appointment FROM appointments GROUP BY doctor;</code>

This counts appointments and finds the minimum appointment ID per doctor.

The key is to ensure a consistent relationship between the grouping criteria and the selected data. Using aggregate functions allows you to summarize data within groups, avoiding the ORA-00979 error.

The above is the detailed content of Why Does My SQL Query Generate ORA-00979: Not a GROUP BY Expression?. For more information, please follow other related articles on the PHP Chinese website!