Some data. I added a row, to account for duplicate surface areas.
CREATE TABLE Table1
(id int, id_biens char(3), surface int)
;
INSERT INTO Table1 VALUES
(1, '001', 80),
(2, '001', 50),
(3, '001', 30),
(4, '001', 55),
(5, '001', 50);
This will work for pairs. It's hard to generalize this to an arbitrary number of rows in SQL. It's essentially a combinatorial problem. You might need to check every possible combination of rows, so in the worst case you'd need to generate and evaluate the set of rows {1, 2, 3, 4, 12, 13, ... 123, 124...1234} (using id numbers here, for example).
For 1000 rows, taking four at a time gives you about 41 billion combinations. Statistical software might be your best bet for this kind of problem.
I think that, in general, the following query is a better approach. (But remember what I said about statistical software.) It changes your output significantly, but I think the change is for the better. It's much clearer which rows make up the total surface.
select distinct b1,
id_1, id_2,
s1, s2,
(s1 + s2) total_surface
from
(select t1.id id_1, t1.id_biens b1, t1.surface s1,
t2.id id_2, t2.id_biens b2, t2.surface s2
from Table1 t1
inner join Table1 t2
on t1.id_biens = t2.id_biens
and t1.id <> t2.id
where t1.id < t2.id
) required_alias
where s1 + s2 between 100 and 105
order by b1, id_1, id_2;
b1 id_1 id_2 s1 s2 total_surface
--
001 2 4 50 55 105
001 2 5 50 50 100
001 4 5 55 50 105
Combinations of three values. Changes you need to make are in comments.
select distinct b1,
id_1, id_2, id_3, -- Add an id,
s1, s2, s3, -- Add a surface
(s1 + s2 + s3) total_surface -- Add surface to the total.
from
(select t1.id id_1, t1.id_biens b1, t1.surface s1,
t2.id id_2, t2.id_biens b2, t2.surface s2,
t3.id id_3, t3.id_biens b3, t3.surface s3 -- Third of three sets of columns.
from Table1 t1
inner join Table1 t2
on t1.id_biens = t2.id_biens
and t1.id <> t2.id
inner join Table1 t3 -- Additional join.
on t1.id_biens = t3.id_biens
and t1.id <> t3.id
where t1.id < t2.id
and t2.id < t3.id -- Additional restriction
) required_alias
where s1 + s2 + s3 between 100 and 105 -- Correct math here, too.
