Good morning, please help to make a SQL query for a sample of such a database of information only about those buyers who bought the same product more than 3 times. My attempts end with Feil: (
SELECT CUSTOMERS.COMPANYNAME FROM CUSTOMERS WHERE CUSTOMERS.CUSTOMERID IN ( SELECT * FROM ORDERS WHERE COUNT(CUSTOMERID)>3)

123

    2 answers 2

     SELECT CUSTOMERS.COMPANYNAME FROM CUSTOMERS WHERE CUSTOMERS.CUSTOMERID IN ( SELECT customerID FROM ORDERS ` group by customerid having COUNT(*)>3)

    Like so

    • Yes. Thank. I 'm stupid after the night :) - stck
    • The subquery is rather select a.CUSTOMERID from ORDERS a inner join ORDER_DETAILS b on a.ORDERID = b.ORDERID group by a.CUSTOMERID, b.PRODUCTID having COUNT(*) > 3 . Skis with apples count separately. - alexlz am
    • additional question: how to find a category in which half of the goods were bought at least once? - stck
    • Half of what? From the entire product range or from something else? - alexlz
    • Yes, from the entire list of products in the category - stck

    If not lied, then:

     select a.CATEGORYID from (select CATEGORYID, count(*) as CNT from PRODUCTS group by CATEGORYID) a inner join (select CATEGORYID, count(*) as CNT from PRODUCTS where PRODUCTID in (select distinct PRODUCTID from ORDER_DETAILS) group by CATEGORYID) b on a.CATEGORYID = b.CATEGORYID having a.CNT <= b.CNT * 2

    The first subquery is the total number of products in the category, the second is the goods for which there is an ORDER. Those products for which there are no sales will be eliminated by the join.

    Corrected. Added grouping in the second subquery.

    • thank! I'll check later - stck