Ora-00979 not a group by expression

sql

join

group-by

sql-best-practices

byAlex Kataev·Nov 9, 2024

Escaping the clutches of the ORA-00979 error is as simple as ensuring that every column in your SELECT statement, not adorned with an aggregate function like SUM, COUNT, etc., finds its place in the GROUP BY clause. Quick course correction, coming up:

-- Incorrect: like making a salad with just cucumbers and calling it Greek
SELECT col1, col2, SUM(col3) FROM your_table GROUP BY col1;

-- Corrected: the real Greek salad now has olives too!
SELECT col1, col2, SUM(col3) FROM your_table GROUP BY col1, col2;

Bullet points worth tattooing on your hand:

Each SELECT column must find its buddy in GROUP BY.
Aggregate functions = party place for non-grouped columns.

A careful deep dive into the "not a group by expression" message would help tune your queries better. Construct your GROUP BY clause right, or risk ending up with ambiguous results or data misinterpretation.

From error to enlightenment: Detailed walkthrough

Samples from the aggregate party: all `GROUP BY` columns

Including all non-aggregate fields in the GROUP BY clause is like sending VIP invites to these columns ensuring 100% attendance for accurate summary creation. Ignore a column, and you risk receiving inconsistent or unexpected data gifts.

The dance of `GROUP BY` and `ORDER BY`

When GROUP BY and ORDER BY decide to tango together, you want to make sure they dance in sync. The ORDER BY should include columns consistent with the GROUP BY data, or else the ordered results may resemble a tangled mess.

Navigating the intricate jungle of joins and filters

Queries with joins or subqueries host multiple tables, turning them into complex mazes. Here, table aliases are your trusty torchlights that bring clarity, prevent confusion and keep ORA-00979 at bay.

-- Employees and Departments tables decide to mingle at a party (also known as a JOIN) 
SELECT e.name, d.department_name, COUNT(*)
FROM employees e JOIN departments d ON e.department_id = d.department_id
GROUP BY e.name, d.department_name;

Here, the aliases (e for employees and d for departments) add clarity like glasses to the nearsighted.

Math tricks up your sleeve

If you want to include a column without grouping, you can do some magic like adding zero to the column:

SELECT col1, MAX(col2 + 0) as col2, SUM(col3)
FROM your_table
GROUP BY col1;

Just remember, this trick, while being cool, is like a jester in the king's court - amusing but not always practical. Use it wisely as it might slow the query's execution or confuse the next developer, who'll probably be wondering why you added zero to a column. Talk about leaving a legacy!

Affiliating aggregated columns with SELECT

The golden rule? Keep non-aggregated columns out of SELECT unless they are part of GROUP BY. Adhering to this can prevent the 'invitation' of ORA-00979.

explain-codes / Sql / Ora-00979 not a group by expression

Linked

Sql - using alias in Group By



How to select unique records by SQL



Using GROUP_CONCAT on subquery in MySQL



Nested select statement in SQL Server



Aggregate function in SQL WHERE-Clause

