group by works, order by gives error msg

[ylekot88]

Hi

I am a student, I am trying to figure this out. I am trying to run a select statement that is asking for customer#, date, and total price of each order. ensure results are in chronological order based upon order date.

When I run the following with just a group by clause, it works. If I add order by orderdate, I get an error message that states SQL Error: ORA-00979: not a GROUP BY expression. Why is the order by clause giving me a group by error message, but the group by works fine? How else would I get the results in chronological order?

[Pilgrim333]

Hi,

Could you post both of the queries you have tried?

Pilgrim.

[ylekot88]

Thanks for getting back to me, I can't believe I forgot to add the code! lol

what I am looking to do:
list customer#, date, and total price of each order. ensure results are in chronological order based upon order date

the following select statement with just a group by (which works) :

select customer#, order#, sum(quantity*retail) "total order"
from orders join orderitems using (order#)
join books using (isbn)
group by customer#, order#;

If I add order by orderdate; after the group by statement, I get this msg:

Error starting at line 5 in command:
select customer#, order#, sum(quantity*retail) "total order"
from orders join orderitems using (order#)
join books using (isbn)
group by customer#, order#
order by orderdate;
Error at Command Line:9 Column:9
Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:

Should I be specifying orderdate in the select statement? (orderdate is in the orderitems table)

[Pilgrim333]

Hi,

Indeed, you should specify the orderdate in de select statement and the group by statement.

Pilgrim.