CODEWITHSUNDEEP
mysql
bruno
bruno

Reputation: 79

generate aggregate results with MySQL select statement

I'm trying to generate a query to tell me how many products were ordered from the site, but in groups.

This is the structure of my table (product_orders):

product_order_id | order_id | product_id
168 | 64 | 17
168 | 64 | 18
168 | 64 | 16
168 | 64 | 15
168 | 64 | 19
168 | 65 | 17
168 | 65 | 18
168 | 66 | 16
168 | 66 | 15
168 | 66 | 19
168 | 67 | 15

What I need to be able to get, is a count of orders where the user purchased:

ONLY product_id 17 AND 18

ONLY product_id 17 AND 16 AND 15

It's that AND that's driving me a bit crazy with this query, and the fact that that 1 order has multiple products. Any ideas? I'm sure I'm overlooking something simple here.

Thanks. B.

Upvotes: 1

Views: 90

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

You can do this with a fairly clunky EXISTS statement

SELECT COUNT(DISTINCT order_id) FROM product_orders p1      
    WHERE EXISTS (SELECT * FROM product_orders p2
                  WHERE p1.order_id = p2.order_id 
                  AND p2.product_id = 17)
    AND EXISTS (SELECT * FROM product_orders p3
                WHERE p1.order_id = p3.order_id 
                AND p3.product_id = 18)
    AND NOT EXISTS (SELECT * FROM product_orders p4
                    WHERE p1.order_id = p4.order_id 
                    AND p4.product_id <> 17
                    AND p4.product_id <> 18);

And you can obviously you repeat this pattern for the {15,16,17} set.

Upvotes: 1

mellamokb
mellamokb

Reputation: 56769

Assuming the product_id's are unique per order in product_orders table, we can count matching and non-matching and compare. So there should be exactly two entries with product_id 17 or 18, and none that are not 17 or 18 to match the first scenario. The second scenario would be the same logic, except there should be exactly three entries with product_id 15 or 16 or 17, and none that don't match any:

select count(*) from (
    select distinct order_id from product_orders po1
    where (
        (select count(product_id) from product_orders po2
        where po1.order_id = po2.order_id and
        po2.product_id in (17, 18)) = 2
    and
        (select count(product_id) from product_orders po3
        where po1.order_id = po3.order_id and
        po3.product_id not in (17, 18)) = 0
    ) or (
        (select count(product_id) from product_orders po4
        where po1.order_id = po4.order_id and
        po4.product_id in (15, 16, 17)) = 3
    and
        (select count(product_id) from product_orders po5
        where po1.order_id = po5.order_id and
        po5.product_id not in (15, 16, 17)) = 0
    )
) p

There is only one order that satisfies all the conditions: order_id 65.

Working Demo: http://sqlize.com/5Q5Lo7Oa71

Upvotes: 0

Related Questions