How to Join & Groupby in SQL?

I am running the following command in Oracle SQL Developer:

SELECT 
  order.adate,
  details.ordid,
  sum(details.price) as total 
FROM details,order 
where details.ordid=order.ordid 
GROUP BY details.ordid;

But I am getting this error:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

Answer

Your select columns don’t match the group by — you have two unaggregated columns in the select and only one in the group by. Presumably, you want either:

select o.adate, d.ordid, sum(d.price) as total 
from details d join
     order o
     on d.ordid = o.ordid 
group by o.adate, d.ordid;

Or more likely:

select o.adate, sum(d.price) as total 
from details d join
     order o
     on d.ordid = o.ordid 
group by o.adate;

Notes:

• Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.
• Table aliases make the query easier to write and to read.
• order is a really bad name for a table because it is a SQL keyword.