How to get the first N rows where the sum is ≥ X?

Question

I have table:

• Products(ID, weight, expired, ...)

I would like to get the N first products which expired is false and sum of the weight is form example >= 5kg.

How could I do this query?

Thanks so much.

EDIT: it seems that I was not very clear in my question. For example I can have this products:

• p1: ID = 1, weight = 1;
• p2: ID = 2, weight = 1;
• p3: ID = 3, weight = 1;
• p4: ID = 4, weight = 2;

For example, I would like to get the first N products which sum of weights is 3. The order is the same.

So I would like to get for example p1, p2 and p3 because the sum of weights is 3 (in this case N is 3 because I need 3 products), so it is correct. I could get p1 and p4 and it would be correct (int this case N is 2 because I need only two products), or p2 and p4... and so on.

Answer 1

You can write as:

select top 10 * from -- top N goes here
 (
 SELECT *,SUM(weight) OVER (partition BY expired ORDER BY Id asc) as cumulativesum
 FROM 
 Products
 WHERE expired = 0
  )T 
  where cumulativesum>=5

DEMO

Answer 2

If you need to sum the weight which is what your question implies try something like

Select ID, [Weight]
From
(select ID, SUM([weight]) as [weight], Row_number() over(order by ID) as N
Where Expired = 0
Having SUM(weight) > = 5) X
where N <= @x

Or if you want heaviest first

Select ID, [Weight]
From
(select ID, SUM([weight]) as [weight], Row_number() over(order by SUM([weight]) desc) as N
Where Expired = 0
Having SUM(weight) > = 5) X
where N <= @x

Where @X is the number required

Answer 3

In order to filter data by column/expression you need to use WHERE clause. For example:

SELECT *
FROM DataSource
WHERE column = 0 -- or column = 'false'

In order to filter data by aggregate expression, you need to use HAVING clause. For example:

SELECT *
FROM DataSource
WHERE column = 0 -- or column = 'false'
HAVING SUM(something) > 100

In order to get TOP records of something you should use appropriate ORDER BY clause either - you need to sort the data in such way that the TOP N will always give you the same results.

Answer 4

SELECT TOP 10 p.*
FROM Prodcts p
WHERE p.expired = 0
AND   p.weight >= 5

However, currently it's not clear how you want to sum a single record.