Why Oracle Is Saying Not a Group by Expression

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

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.

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.

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;

not a GROUP BY expression

Every column listed in the SELECT that is not an aggregate function result (SUM(), AVG(), COUNT(), MIN(), MAX() and so forth) has to be listed in the GROUP BY. You've selected B.AFLEVERING_ID and A.NAAM, so your group by has to include both of them.

SELECT B.AFLEVERING_ID,A.NAAM, COUNT(B.AFLEVERING_ID) AS aantal_keer_bekeken
FROM BEKEKENVIA B, AFLEVERING A
WHERE A.AFLEVERING_ID = B.AFLEVERING_ID
GROUP BY B.AFLEVERING_ID, A.NAAM
ORDER BY B.AFLEVERING_ID ASC;

Encountering ORA-00979: not a GROUP BY expression when using CASE - IN statements in sql

I cannot reproduce this error with following request (working):

select (case when 'X' IN (select dummy from dual where rownum=1) then 1 else 0 end)
from dual
where dummy = 'X'
group by (case when 'X' IN (select dummy from dual where rownum=1) then 1 else 0 end)
;

Try:

WITH table1_extended AS (
   SELECT a.*, CASE WHEN x IN .... END "condition"
     FROM table1 a
)
SELECT b."condition"
  FROM table1_extended b
 WHERE b.this = 'that'
 GROUP BY b."condition"

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

---

Related Topics