How to identify rows that add up (sum) to a specific amount

Question

I am trying to identify rows in a specific dataset with a dollar amount column that sum up to a specific amount. I know the amount and the amount of rows that add up to that amount.

Parameters: 4 records to sum up to $15.68

dataset is > 450k records

so is there a way to find 4 rows that sum up to $15.68

say the table has a unique Identifier for each row called ID and then a corresponding amount column

ID     Amount
22     $11.55
33     $5.55
44     $3.33
55     $2.33
66     $1.11
77     $.99
88     $-1.77
91     $0.00
99     $-5.00
100    $3.33
111    $4.44
122    $5.00
133    $4.00
144    $2.24

so I am looking to get a result set of the last 4 records out of this sample set.

Answer 1

Use INNER JOIN to join the table to itself 4 times and join so that the values are in ascending order so that you do not repeated combinations in different orders or rows repeatedly joined to themselves:

SELECT d1.id AS id1,
       d1.amount AS amount1,
       d2.id AS id2,
       d2.amount AS amount2,
       d3.id AS id3,
       d3.amount AS amount3,
       d4.id AS id4,
       d4.amount AS amount4
FROM   data d1
       INNER JOIN data d2
       ON (d1.id < d2.id)
       INNER JOIN data d3
       ON (d2.id < d3.id)
       INNER JOIN data d4
       ON (d3.id < d4.id)
WHERE  d1.amount + d2.amount + d3.amount + d4.amount = 15.68;

Which outputs:

ID1	AMOUNT1	ID2	AMOUNT2	ID3	AMOUNT3	ID4	AMOUNT4
111	4.44	122	5	133	4	144	2.24

---

If you want the values as rows then UNPIVOT:

SELECT id,
       amount
FROM   (
  SELECT d1.id AS id1,
         d1.amount AS amount1,
         d2.id AS id2,
         d2.amount AS amount2,
         d3.id AS id3,
         d3.amount AS amount3,
         d4.id AS id4,
         d4.amount AS amount4
  FROM   data d1
         INNER JOIN data d2
         ON (d1.id < d2.id)
         INNER JOIN data d3
         ON (d2.id < d3.id)
         INNER JOIN data d4
         ON (d3.id < d4.id)
  WHERE  d1.amount + d2.amount + d3.amount + d4.amount = 15.68
)
UNPIVOT (
  (amount, id) FOR type IN (
    (amount1, id1) AS 1,
    (amount2, id2) AS 2,
    (amount3, id3) AS 3,
    (amount4, id4) AS 4
  )
);

Which outputs:

ID	AMOUNT
111	4.44
122	5
133	4
144	2.24

---

You can also solve it with a recursive query:

WITH hierarchy (id1, amount1, id2, amount2, id3, amount3, id4, amount4, lvl, total) AS (
  SELECT id, amount,
         CAST(NULL AS NUMBER), CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER), CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER), CAST(NULL AS NUMBER),
         1, amount
  FROM   data
 UNION ALL
  SELECT h.id1,
         h.amount1,
         CASE lvl + 1 WHEN 2 THEN d.id     ELSE h.id2 END,
         CASE lvl + 1 WHEN 2 THEN d.amount ELSE h.amount2 END,
         CASE lvl + 1 WHEN 3 THEN d.id     ELSE h.id3 END,
         CASE lvl + 1 WHEN 3 THEN d.amount ELSE h.amount3 END,
         CASE lvl + 1 WHEN 4 THEN d.id     ELSE h.id4 END,
         CASE lvl + 1 WHEN 4 THEN d.amount ELSE h.amount4 END,
         lvl + 1, amount + total
  FROM   data d
         INNER JOIN hierarchy h
         ON COALESCE(h.id3, h.id2, h.id1) < d.id AND h.lvl < 4
)
SELECT id, amount
FROM   (
  SELECT *
  FROM   hierarchy
  WHERE  lvl = 4
  AND    total = 15.68
)
UNPIVOT (
  (amount, id) FOR type IN (
    (amount1, id1) AS 1,
    (amount2, id2) AS 2,
    (amount3, id3) AS 3,
    (amount4, id4) AS 4
  )
);

Which also outputs:

ID	AMOUNT
111	4.44
122	5
133	4
144	2.24

fiddle