Search code examples
sqlsqlitejoincountinner-join

Count how many different combinations add up to certain value

I have the following table.

CREATE TABLE product (
   id INTEGER PRIMARY KEY,
   name TEXT,
   price INT
);

I need to find out the number of 2 product combinations which add up to a total price of 15.

Same combination can only be used once so for example if apple+lemon combination was used lemon+apple can not be used anymore. Also apple+apple would be a valid combination.

Here is the code I have got so far but the results are far off. Any idea for a query which would do that?

SELECT COUNT(*) 
FROM 
    Product p1, Product p2 
WHERE 
    p1.price + p2.price = 10;
Solution

  • I think you want:

    select count(*) 
from product p1
inner join product p2 on p1.id < p2.id
where p1.price + p2.price = 10;

    That's pretty much the same query as yours, but with an inequality condition on the product name, so that each tuple is counted only once - which seems to be what you ask for.