Thread

I’m trying to build an admin section for an e-commerce site (not powered by EE for anything sadly) and needing a helping hand with the SQL.

I want to display the top 10 selling products.

Obviously products are in a products table, I have an orders table which contains a summary of the order and also an ordersdetail table that holds products ordered, quantity etc.

Any ideas on where to start? I had thought along the lines of

SELECT *
FROM products,  orderdetails
WHERE products.ProdID = orderdetails.DetailProductID
ORDER BY orderdetails.DetailQuantity

but no such joy there.

should there be a COUNT or SUM statement?

should there be a COUNT or SUM statement?

Almost.  Normally when you use SUM, it adds up all values in a column.  That’s almost right - but we don’t want the entire column.  Instead you first create groups based on another column and then run the sum on those individually (that’s what GROUP BY does).

Something along these lines:

SELECT products.name, products.ProdId, SUM(orderdetails.DetailQuantity) as Quantity
FROM products,  orderdetails
WHERE products.ProdID = orderdetails.DetailProductID
GROUP BY orderdetails.DetailProductID
ORDER BY Quantity

SUM would work and you are almost there.
Try:

SELECT
    products.ProdName,
    SUM(orderdetails.DetailQuantity) as "Total QTY Sold"
FROM
    products
    INNER JOIN orderdetails 
        ON (products.ProdID = orderdetails.DetailProductID)
GROUP BY (products.ProdID)
ORDER BY SUM(orderdetails.DetailQuantity) DESC LIMIT 10;

Depending what columns you ‘SELECTED’ will be returned in results.In the case above “product name” and “total qty sold”

edit: I am too slow posting Pascal beat me 😊