asked
How Do I Subquery this?
I need to insert this query SELECT DISTINCT COUNT(CustomerID) AS NumUnique FROM Products, Orders, OrderDetails WHERE (((Products.ProductID)=OrderDetails.ProductID) And ((OrderDetails.OrderID)=Orders.OrderID)) GROUP BY Products.ProductID, Products.ProductName inside this query SELECT Products.ProductID, Products.ProductName, COUNT (CustomerID) AS NumCustomersOrdered FROM Products, Orders, OrderDetails WHERE (((Products.ProductID)=OrderDetails.ProductID) And ((OrderDetails.OrderID)=Orders.OrderID)) GROUP BY Products.ProductID, Products.ProductName; im having trouble with the format
Your first Query Counts the number of occurences of CustomerID, grouped by producctid and productname, and makes a distinct selection of these. So if for productID = 1, there are five rows having a CustomerID, and for ProductID = 2, there are also five rows having CustomerID, only one of these rows are returned. I don't really understand how that's related to your next Query. If you want to put something in a subquery, I'm assuming it is somehow related to the outer Query, but I don't make any sense out of it. Please describe in Words what it is you want to achieve. Could it be that you in fact want to Count the number of distinct CustomerIDs per Product? If that's the case, you would want to move the DISTINCT keyword inside the COUNT-function-call. Like this SELECT COUNT(DISTINCT CustomerID) as NumUnique FROM Products, Orders, OrderDetails etc. Another remark I have is that I prefer using ANSI-92 syntax for joins, because it is much more clear what's a join-condition and what's a where-condition. The syntax you use is perfectly valid for an INNER JOIN, but for a LEFT, RIGHT or FULL Outer join, there's no valid syntax using ANSI-89-syntax. Like this: SELECT blablabla FROM Products INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
i understand about the joins but its just an inner join. I need to show in my query, the productid, productname, the number of customers who bought the product ( the same customer who ordered the same product in different orders counts as 2 or more separate orders), and the number of unique customers who bought the product. right now i have this except the distinct customer id is returning the same value in every row SELECT Products.ProductID, Products.ProductName, COUNT (CustomerID) AS NumCustomersOrdered, (SELECT DISTINCT COUNT(CustomerID) AS NumUnique FROM Products, Orders, OrderDetails WHERE (((Products.ProductID)=OrderDetails.ProductID) And ((OrderDetails.OrderID)=Orders.OrderID))) FROM Products, Orders, OrderDetails WHERE (((Products.ProductID)=OrderDetails.ProductID) And ((OrderDetails.OrderID)=Orders.OrderID)) GROUP BY Products.ProductID, Products.ProductName;
17 People are following this question.
