1

what is wrong with this sql statement, i keep getting this error:

SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action:

SELECT orderdetails.orderid, 
     orderdate, 
     sum (quantity *( unitprice - unitprice * discount))as "Order Total" 
FROM orderdetails
    INNER JOIN orders orders on orderdetails.orderid = orders.orderid
WHERE customerid = 'ERNSH'
GROUP BY orderdetails.orderid
ORDER BY "Order Total" desc;

What if i have another variable like Shipped ??

SELECT orderdetails.orderid, orderdate, NVL2(Shippeddate, "Not Shipped" , "Shipped" ) as "Shipped" ,sum (quantity *( unitprice - unitprice * discount))as "Order Total" FROM orderdetails
INNER JOIN orders orders on orderdetails.orderid = orders.orderid
WHERE customerid = 'ERNSH'
GROUP BY orderdetails.orderid , orderdate, Shipped
ORDER BY "Order Total" desc;
user2371290
  • 297
  • 2
  • 3
  • 8
  • 3
    Add orderdate to the GROUP BY clause --- `group by orderdetails.orderid, orderdate` – Gurwinder Singh Aug 03 '17 at 18:19
  • 1
    Any reason you aliased the table `orders` as `orders`? The only possible use of that is so you can refer to it by the name `orders` instead of the name `orders`. I don't see the point... –  Aug 03 '17 at 20:32

1 Answers1

2

Either add orderdate to the GROUP BY clause:

select orderdetails.orderid,
    orderdate,
    sum(quantity * (unitprice - unitprice * discount)) as "Order Total"
from orderdetails
inner join orders orders on orderdetails.orderid = orders.orderid
where customerid = 'ERNSH'
group by orderdetails.orderid, orderdate
order by "Order Total" desc;

or use an aggregate function on the column if it's not supposed to be in the group by clause:

select orderdetails.orderid,
    max(orderdate) as orderdate,   ---  see which function is suitable
    sum(quantity * (unitprice - unitprice * discount)) as "Order Total"
from orderdetails
inner join orders orders on orderdetails.orderid = orders.orderid
where customerid = 'ERNSH'
group by orderdetails.orderid
order by "Order Total" desc;

The above two are syntactically correct, but logically you have to decide.

Also, try to use smaller aliases to cleanly write the query if possible and consistently qualify the column names with alias/table names.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • what is the reason we have to include the orderdate , however we don't have to include the "Order Total " in the group by clause ? like group by orderdetails.orderid, orderdate, "Order Total" – user2371290 Aug 03 '17 at 23:24
  • The `GROUP BY` clause must have all the non aggregated columns/expression present in the `SELECT` clause. The "Order Total" is a result of aggregation. So, not present in the group by. This is exactly why I didn't need to include orderdate in the group by clause in the second query - because we are using an aggregate function on that column. – Gurwinder Singh Aug 04 '17 at 03:27
  • What if i have another variable like Shipped ?? SELECT orderdetails.orderid, orderdate, NVL2(Shippeddate, "Not Shipped" , "Shipped" ) as "Shipped" ,sum (quantity *( unitprice - unitprice * discount))as "Order Total" FROM orderdetails INNER JOIN orders orders on orderdetails.orderid = orders.orderid WHERE customerid = 'ERNSH' GROUP BY orderdetails.orderid , orderdate, Shipped ORDER BY "Order Total" desc; – user2371290 Aug 04 '17 at 04:56