ORA-00979 not a group by expression
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
?
oracle sql error: ORA-00979: not a GROUP BY expression
GROUP BY
is required for aggregating functions, such as SUM()
or COUNT()
. So a query like select * from politics group by WASDEPENDENT
does not make sense.
Here is an example of the correct usage. This query counts how many records fall into each value of WASDEPENDENT
:
select WASDEPENDENT, count(*)
from politics
group by WASDEPENDENT
order by WASDEPENDENT
SQL not a GROUP BY expression with OracleSQL and InnerQuery Error
As you can see in the ORACLE docs
SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.
This means that if you only group by o.CUSTOMER_ID
, all the other fields in the select list must be aggregate functions (like COUNT, MAX, etc.).
In the case of fields that repeat values in each group (as name and surname) you should include them in the GORUP BY clause.
To include the sum of money spent, you could add another LEFT JOIN with PRODUCTS and select SUM(op.amount*p.price)
without a subquery.
That would be
SELECT o.CUSTOMER_ID AS "ID", c.name AS "Name", c.SURNAME AS "Surname",
SUM(op.AMOUNT*p.PRICE) AS "Money spent"
FROM ORDERS o
LEFT JOIN CUSTOMERS c ON c.ID = o.CUSTOMER_ID
LEFT JOIN ORDERS_PRODUCTS op ON op.ORDER_ID = o.id
LEFT JOIN PRODUCTS p ON p.id = op.PRODUCT_ID
GROUP BY o.CUSTOMER_ID, c.name AS "Name", c.SURNAME
ORDER BY o.CUSTOMER_ID, c.name AS "Name", c.SURNAME;
Remember always to define the sort order of your queries, otherwise it will be undefined.
Oracle SQL GROUP BY not a GROUP BY expression help
select id, other_id, date_value, value from
(
SELECT id, other_id, date_value, value,
ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
FROM some_table
WHERE other_id IN (1, 2, 3)
)
where r = 1
ORA-00979: not a GROUP BY expression with a simple example
In the SELECT clause, you can only refer to expression that either appear in the GROUP BY clause or are aggregations (such as SUM). c.recipe_name
does not qualify as such.
You might know that grouping by a.recipe_id
will lead to a unique result for c.recipe_name
(within each group). And Oracle might even be able to derive this information as well. But SQL is more strict and requires you to put the expression in the GROUP BY clause.
So, just write:
SELECT c.recipe_name,
COUNT(a.ingredient_id),
SUM(a.amount*b.ingredient_price)
FROM recipe_ingredients a
JOIN ingredients b
ON a.ingredient_id = b.ingredient_id
JOIN recipes c
ON a.recipe_id = c.recipe_id
GROUP BY a.recipe_id, c.recipe_name;
SQL Query not working ORA-00979: not a GROUP BY expression
You need to add firstname
to your GROUP BY
GROUP BY firstname, lecturerID
You use an aggregate function (COUNT) on ModuleCode, so any remaining columns must either be aggregate functions (SUM/COUNT/MIN/MAX) or included in group by
(Is this a homework question?)
Group By expression not working in this SQL query?
The error is with group by clause. Remember simple rule of thumb, all columns being selected to be in group by clause, or the columns to be selected which are not part of group by clause are to be selected as some aggregate function, like, MAX, MIN, SUM, AVG, etc.
Try the following query, which would run without issue. But I can't say its logical correctness which you need to figure out on your requirement basis.
CREATE OR REPLACE VIEW customer_line_item AS
SELECT cac.loginName,
cac.FirstName,
cac.LastName,
cac.orderCartID,
(SUM(li.orderPrice) * SUM(li.qtyOrdered)) AS TOTAL_ORDER
FROM customer_order_cart_info cac
INNER JOIN lineItems li
ON cac.orderCartID = li.orderCartID
GROUP BY cac.loginName,
cac.FirstName,
cac.LastName,
cac.orderCartID
ORDER BY cac.orderCartID;
Now thing to note here is, li.orderPrice and li.qtyOrdered were being selected, but were neither in group by nor in a aggregate function.
The use of group by is that, the columns in group by clause are used to logically group your data. Here your data is grouped by loginName, firstname, lastname, ordercartid. But there is a probability that multiple orderprice and qty exist for each group, and SQL is not able to justify the grouping logic then. Per your query one requirement that I could think of was, you want find the total value of order for a customer in his cart. Hence, you are multiplying orderPrice with qtyOrdered. To achieve this, you need to multiply orderPrice and orderqty of each lineItem. Hence, what you need is a sum of (orderPrice*orderQty) group by lineItem(lineItemID/lineItemNo maybe, just a guess). For this one, give me some time, let me devise an example and I will edit my answer with that. Till then you try something like above.
ORA-00979 : select distinct(concat column) not a group by expression
You may repeat the entire expression in the GROUP BY
clause as it appears in the SELECT
:
SELECT DISTINCT colA || SUBSTR(colB, 1, 2) AS colA
FROM myTable
WHERE colC = '5678'
GROUP BY colA || SUBSTR(colB, 1, 2);
Oracle does not supporting grouping using aliases, or using 1
, 2
, etc. positional parameters. And note that DISTINCT
is not a SQL function.
Related Topics
How to Best Handle the Storage of Historical Data
Get the List of Stored Procedures Created And/Or Modified on a Particular Date
Cycle Detection with Recursive Subquery Factoring
Sql Server 2008 - Case/If Statements in Select Clause
Rails Activerecord Query Using Inner Join
Formatting Clear and Readable SQL Queries
How to Fetch The Nth Highest Salary from a Table Without Using Top and Sub-Query
How to Troubleshoot Ora-02049 and Lock Problems in General with Oracle
Replacing Only The Nth Instance of Character
Sql Server 2005 Unique Constraint on Two Columns
SQL Query for Courses Enrolment on Moodle
Sql Parentheses Use in an or Clause
Are There Any Limits on Length of String in MySQL
PHP Is Truncating Mssql Blob Data (4096B), Even After Setting Ini Values. am I Missing One
Bigquery Select _Tables_ from All Tables Within Project