How do I use SQL to select rows that have > 50 related rows in another table?

Question

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
      `productname` 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'

Answer 1

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.

Answer 2

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