SQL, W3school practice problem - find the product that was ordered most with by customers in France-CodePudding

doing a practice problem on W3school - the database is here: https://www.w3schools.com/SQL/TRYSQL.ASP?FILENAME=TRYSQL_SELECT_ALL

I'm trying to return the Product (found in table OrderDetails) that was ordered the most by customers in the Country France (Country is found in the table Customers). To do this I first had to join across 3 tables: first by joining OrderDetails and Orders on OrderID, and then joining the merged table with Customers on CustomerID. Problem is, now that I have the table, I can't seem to figure out how to return the ProductID that was ordered the most by customers in France.. The following code:

SELECT
    c.customer_id,
    SUM(od.Quantity) AS totalQuantity
    FROM (OrderDetails od LEFT JOIN Orders o ON od.OrderID = o.OrderID)
    LEFT JOIN Customers c ON o.CustomerID = c.CustomerID
    GROUP BY od.ProductID
    ORDER BY totalQuantity DESC
    LIMIT 1;

returns the error:

Syntax error in ORDER BY clause

Any advice on what I'm doing wrong here? Is what I'm doing even getting close to returning the ProductID that was ordered the most by customers in France?

CodePudding user response：

LIMIT isn't Access SQL syntax, so try:

SELECT TOP 1
    od.ProductID,
    SUM(od.Quantity) AS totalQuantity
FROM 
    (OrderDetails AS od 
LEFT JOIN 
    Orders AS o ON od.OrderID = o.OrderID)
LEFT JOIN 
    Customers AS c ON o.CustomerID = c.CustomerID
GROUP BY 
    od.ProductID
ORDER BY 
    SUM(od.Quantity) DESC;

CodePudding user response：

W3Schools is using WebSQL that connects to a SqLite 3

You can check it's version :

SELECT sqlite_version();

The query in the question doesn't work because it selects c.customer_id, which should be c.CustomerID.

This query will return the product with the most unique customers in France.

The country is taken from the Customers table.
The Products table is only joined to get the name of the product.

SELECT 
  od.ProductID
, p.ProductName
, COUNT(DISTINCT o.OrderID) AS TotalOrders
, COUNT(DISTINCT c.CustomerID) AS TotalCustomers
, SUM(od.Quantity) AS TotalQuantity
FROM "OrderDetails" AS od
INNER JOIN "Orders" AS o ON o.OrderID = od.OrderID
INNER JOIN "Customers" AS c ON c.CustomerID = o.CustomerID
INNER JOIN "Products" AS p ON p.ProductID = od.ProductID
WHERE c.Country = 'France' 
GROUP BY od.ProductID, p.ProductName
ORDER BY TotalCustomers DESC
LIMIT 1

It's a meat pie.