You must put all columns of the SELECT
in the GROUP BY
or use functions on them which compress the results to a single value (like MIN
, MAX
or SUM
).
A simple example to understand why this happens: Imagine you have a database like this:
FOO BAR
0 A
0 B
and you run SELECT * FROM table GROUP BY foo
. This means the database must return a single row as result with the first column 0
to fulfill the GROUP BY
but there are now two values of bar
to chose from. Which result would you expect - A
or B
? Or should the database return more than one row, violating the contract of GROUP BY
?
Include in the GROUP BY
clause all SELECT
expressions that are not group function arguments.
Too bad Oracle has limitations like these. Sure, the result for a column not in the GROUP BY would be random, but sometimes you want that. Silly Oracle, you can do this in MySQL/MSSQL.
BUT there is a work around for Oracle:
While the following line does not work
SELECT unique_id_col, COUNT(1) AS cnt FROM yourTable GROUP BY col_A;
You can trick Oracle with some 0's like the following, to keep your column in scope, but not group by it (assuming these are numbers, otherwise use CONCAT)
SELECT MAX(unique_id_col) AS unique_id_col, COUNT(1) AS cnt
FROM yourTable GROUP BY col_A, (unique_id_col*0 + col_A);
If you do grouping by virtue of including GROUP BY
clause, any expression in SELECT
, which is not group function (or aggregate function or aggregated column) such as COUNT
, AVG
, MIN
, MAX
, SUM
and so on (List of Aggregate functions) should be present in GROUP BY
clause.
Example (correct way) (here employee_id
is not group function (non-aggregated column), so it must appear in GROUP BY
. By contrast, sum(salary) is a group function (aggregated column), so it is not required to appear in the GROUP BY
clause.
SELECT employee_id, sum(salary)
FROM employees
GROUP BY employee_id;
Example (wrong way) (here employee_id
is not group function and it does not appear in GROUP BY
clause, which will lead to the ORA-00979 Error .
SELECT employee_id, sum(salary)
FROM employees;
To correct you need to do one of the following :
SELECT
clause in the
GROUP BY
clauseSELECT
clause.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With