I've been trying to find out how to write this query in sql.
What I need is to find the productnames (in the products table) that have 50 or more orders (which are in the order table).
only one orderid is matched up to a productname at a time so when I try to count the orderid's it counts all of them.
I can get distinct productnames but once i add in the orderid's then it goes back to having multiple productnames.
I also need to count the number of customers (in the order table) that have ordered those products.
I need some serious help ASAP! if anyone could help me figure out how to figure this out that would be awesome!
Table: Products
`produ开发者_JS百科ctname` in the form of a text like 'GrannySmith'
Table: Orders
`orderid` in the form of '10222'..etc
`custid` in the form of something like 'SMITH'
Assuming the orders table has a field that relates back to the products table named ProductId. The SQL would translate to:
SELECT p.ProductName, Count(*)
FROM Orders o
JOIN Products p
on o.ProductId = p.ProductId
GROUP BY p.ProductName HAVING COUNT(*) >= 50
The key is in the having
component of the Group By clause. I hope this helps.
You might be missing an "Order Details" table - typically, an order has several order details, and each of the order details then maps to a product - something like the sample in Northwind:
In that case, your SQL query would be something like this: join the [Order Details]
table to both the [Orders]
and [Products]
tables, group by the product ID and name, and count the OrderID's:
select
p.ProductID, p.ProductName, count(o.OrderID)
from
[order details] od
inner join
orders o on od.OrderID = o.OrderID
inner join
products p ON od.productID = p.ProductID
group by
p.ProductID, p.ProductName
having
count(o.OrderID) > 50
精彩评论