I am getting this error ORA-00907: missing right parenthesis and cant find a way to fix-CodePudding

im in university and i have a exercise where im having a hard time finding a solution.

The question is: "Present a list with the amount of different products present in each purchase order made for customers in Norway (Norway)."

I tried this code:

SELECT ORDERS.ORDERID, COUNT(PRODUCTS.PRODUCTSID) AS NUMPRODUCTS
FROM PRODUCTS GROUP BY ORDERS.ORDERID
INNER JOIN ORDERSDETAILS ON PRODUCTS.PRODUCTID=ORDERDETAILS.PRODUCTID
INNER JOIN ORDERS ON ORDERDETAILS.ORDERID=ORDERS.ORDERID
INNER JOIN CUSTOMERS ON ORDERS.CUSTOMERSID=CUSTOMERS.CUSTOMERSID
WHERE (CUSTOMERS.COUNTRY='Norway');

But it gives me a error ORA-00907: missing right parenthesis. Can you tell me what is the error in the code?

CodePudding user response：

Parenthesis isn't missing; that's a misleading error message. True problem is in clauses' order; group by should go to the end of that query:

SELECT
    orders.orderid,
    COUNT(products.productsid) AS numproducts
FROM
         products
    INNER JOIN ordersdetails ON products.productid = orderdetails.productid
    INNER JOIN orders ON orderdetails.orderid = orders.orderid
    INNER JOIN customers ON orders.customersid = customers.customersid
WHERE ( customers.country = 'Norway' )
GROUP BY orders.orderid;                       --> here

CodePudding user response：

I recommend using careful code layout as a first step in tracing syntax errors.

Failing code, laid out neatly:

select o.orderid
     , count(p.productsid) as numproducts
from   products p
group  by o.orderid  -- error now a bit more obvious
       join ordersdetails d on  d.productid = p.productid
       join orders o on  o.orderid = d.orderid
       join customers c on  c.customersid = o.customersid
where  c.country = 'Norway';

Now easily fixed as:

select o.orderid
     , count(p.productsid) as numproducts
from   products p
       join ordersdetails d on  d.productid = p.productid
       join orders o on  o.orderid = d.orderid
       join customers c on  c.customersid = o.customersid
where  c.country = 'Norway'
group  by o.orderid;