I am trying to write a query to display customer name, orderID, and productID for the orders that have more than one product.

This is using W3Schools learning Database (unsure how to share this).

I currently can display everything but I need to cull the rows that are orders that have only one product. I've attempted to use HAVING COUNT to count the rows that have the same OrderID in the OrderDetails table but for some reason it returns for every individual row and it returns 1. I'm not sure what I'm doing wrong.

SQL Code:

    SELECT Customers.CustomerName, Orders.OrderID, Products.ProductID, 
    COUNT(OrderDetails.OrderID) as NumberOfProducts
    FROM OrderDetails
    INNER JOIN Orders
        ON Orders.OrderID=OrderDetails.OrderID
    INNER JOIN Customers
        ON Customers.CustomerID=Orders.CustomerID
    INNER JOIN Products
        ON Products.ProductID=OrderDetails.ProductID
    GROUP BY Orders.OrderID, Customers.CustomerName, Products.ProductID
    HAVING COUNT(OrderDetails.OrderID) > 2;

This is my first time posting so please let me know if there's anymore information I could share to make things more clear!

CodePudding user response：

It's because your GROUP BY statement has 3 clauses. This means that only rows with the same OrderID, CustomerName, and ProductID will be grouped together. However, most rows will differ in at least one of those columns so they will not be grouped. If you make you GROUP BY less specific then the COUNT will be greater than one.