Mysql select rows where sum is equal to

Question

I have a table A like this :

ID | AMOUNT
1  | 10
2  | 30
3  | 40

I will want to find the lines with the sum of the amount equal to a value.

For example if I put the total amount to 70, my query must return id 2 and 3

Answer 1

You know for every row what the value of the linked row should be (70-amount) so given this you can generate min and max amounts for every row, generate a blockid based on the minamount and a rownumber within the block. For example given

+------+--------+
| id   | amount |
+------+--------+
|    1 |     10 |
|    2 |     30 |
|    3 |     40 |
|    4 |     70 |
|    5 |     35 |
|    6 |     35 |
|    7 |     35 |
|    8 |     35 |
|    9 |     40 |
|   10 |     60 |
|   11 |     60 |
+------+--------+
11 rows in set (0.00 sec)

select id oldid,amount,
        70 - amount matching_amount,
        case when amount = 70 then 0
            when amount < 70 - amount then amount
            when amount >= 70 - amount then 70 - amount
        end as minamount,
        case when amount = 70 then amount
            when amount < 70 - amount then 70 - amount
            when amount >= 70 - amount then amount
        end as maxamount
from t
) s
cross join (select @bn:=0,@rn:=0,@pid:=0,@pmin:=999999) b
order by minamount,oldid;

You get

+-------+-----------+-----------+---------+-----------+------+
| oldid | minamount | maxamount | blockid | rownumber | pmin |
+-------+-----------+-----------+---------+-----------+------+
|     4 |         0 |        70 | 1       |         1 |    0 |
|     1 |        10 |        60 | 2       |         1 |   10 |
|    10 |        10 |        60 | 2       |         2 |   10 |
|    11 |        10 |        60 | 2       |         3 |   10 |
|     2 |        30 |        40 | 3       |         1 |   30 |
|     3 |        30 |        40 | 3       |         2 |   30 |
|     9 |        30 |        40 | 3       |         3 |   30 |
|     5 |        35 |        35 | 4       |         1 |   35 |
|     6 |        35 |        35 | 4       |         2 |   35 |
|     7 |        35 |        35 | 4       |         3 |   35 |
|     8 |        35 |        35 | 4       |         4 |   35 |
+-------+-----------+-----------+---------+-----------+------+
11 rows in set (0.00 sec)

If you then do the same again and left join odd to even rows within blockid

select x.oldid,x.minamount,x.maxamount,x.blockid,x.rownumber, y.oldid,
        y.minamount,y.maxamount,y.blockid,y.rownumber
from
(
select oldid,minamount,maxamount,
        if(minamount <> @pmin,@bn:=@bn+1,@bn:=@bn) blockid,
        if(minamount <> @pmin,@rn:=1,@rn:=@rn+1) rownumber,
        @pmin:=minamount pmin
from
(
select id oldid,amount,
        70 - amount matching_amount,
        case when amount = 70 then 0
            when amount < 70 - amount then amount
            when amount >= 70 - amount then 70 - amount
        end as minamount,
        case when amount = 70 then amount
            when amount < 70 - amount then 70 - amount
            when amount >= 70 - amount then amount
        end as maxamount
from t
) s
cross join (select @bn:=0,@rn:=0,@pid:=0,@pmin:=999999) b
order by minamount,oldid
) x
left join
(
select oldid,minamount,maxamount,
        if(minamount <> @pmin1,@bn1:=@bn1+1,@bn1:=@bn1) blockid,
        if(minamount <> @pmin1,@rn1:=1,@rn1:=@rn1+1) rownumber,
        @pmin1:=minamount pmin
from
(
select id oldid,amount,
        70 - amount matching_amount,
        case when amount = 70 then 0
            when amount < 70 - amount then amount
            when amount >= 70 - amount then 70 - amount
        end as minamount,
        case when amount = 70 then amount
            when amount < 70 - amount then 70 - amount
            when amount >= 70 - amount then amount
        end as maxamount
from t
) a
cross join (select @bn1:=0,@rn1:=0,@pid1:=0,@pmin1:=999999) b
order by minamount,oldid
) y
on y.blockid = x.blockid and y.rownumber = x.rownumber + 1
where (x.rownumber % 2 > 0 and y.oldid is not null) or
        (x.rownumber % 2 > 0 and x.minamount = 0)
order by x.oldid;

You get

+-------+-----------+-----------+---------+-----------+-------+-----------+-----------+---------+-----------+
| oldid | minamount | maxamount | blockid | rownumber | oldid | minamount | maxamount | blockid | rownumber |
+-------+-----------+-----------+---------+-----------+-------+-----------+-----------+---------+-----------+
|     1 |        10 |        60 | 2       |         1 |    10 |        10 |        60 | 2       |         2 |
|     2 |        30 |        40 | 3       |         1 |     3 |        30 |        40 | 3       |         2 |
|     4 |         0 |        70 | 1       |         1 |  NULL |      NULL |      NULL | NULL    |      NULL |
|     5 |        35 |        35 | 4       |         1 |     6 |        35 |        35 | 4       |         2 |
|     7 |        35 |        35 | 4       |         3 |     8 |        35 |        35 | 4       |         4 |
+-------+-----------+-----------+---------+-----------+-------+-----------+-----------+---------+-----------+
5 rows in set (0.10 sec)

In Version 8 or above row number simulation can be simplified with row_number function

Answer 2

Below query will return list of two rows whose sum is equal to your passed value. If there are more than one combinations of rows matching total sum it will return multiple lists. Check this on Fiddle. You just need to pass required total sum instead of 70

SELECT If((t1.amount+t2.amount)=6,CONCAT(LEAST(t1.id,t2.id),',',GREATEST(t1.id,t2.id)),"") AS sumIds 
FROM testSum t1, testSum t2
WHERE (t1.amount+t2.amount)=6
AND t1.id!=t2.id
GROUP BY sumIds