Find a list of all suppliers with orders from more than 615 different customers. Should return supplier name and customer count

Tables:

• Supplier: S_SUPPKEY, S_NAME
• Customer : C_CUSTKEY, C_NAME
• Orders : O_ORDERKEY, O_CUSTKEY
• Lineitem : L_ORDERKEY, L_SUPPKEY

In the Lineitem table, there are many columns with the same ORDERKEY, but from different SUPPKEY. There can be multiple suppliers in a single order. So if you have 3 suppliers in a single order for one customer, that customer would count for each of the 3 suppliers in their customer count.

SELECT 
    S_NAME,
    (SELECT COUNT(Customer.C_NAME)
     FROM Customer
     JOIN Orders ON Customer.C_CUSTKEY = Orders.O_CUSTKEY
     JOIN Lineitem ON Orders.O_ORDERKEY = Lineitem.L_ORDERKEY
     WHERE Lineitem.L_SUPPKEY = Supplier.S_SUPPKEY
     GROUP BY Lineitem.L_SUPPKEY) AS "customer count"
FROM 
    Supplier
WHERE 
    "customer count" > 615;

This is the query that I came up with, but it seems to be counting duplicate orders, since one order can have many items. I can’t figure out a way to fix this.

Please help

Answer

Use COUNT(DISTINCT):

SELECT s.s_suppkey, s.s_name
FROM Orders o JOIN
     LineItem li
     ON li.L_ORDERKEY = o.O_ORDERKEY JOIn
     Supplier s
     ON li.L_SUPPKEY = s.S_SUPPKEY
GROUP BY s.s_suppkey, s.s_name
HAVING COUNT(DISTINCT o.O_CUSTKEY) > 615;

Note that you don’t need the customer table.