Get rows in SQL by summing up a until certain value is exceeded and stop retrieving

Question

I have to return rows from the database when the value exceeds a certain point.

I should get enough rows to sum up to a value that is greater than my quantity and stop retrieving rows.

Is this possible and does it makes sense? Can this be transferred into LINQ for EF core?

I am currently stuck with query that will return all the rows...

SELECT [i].[InventoryArticleId], [i].[ArticleId], [i].[ArticleQuantity], [i].[InventoryId]
      FROM [InventoryArticle] AS [i]
      INNER JOIN [Article] AS [a] ON [i].[ArticleId] = [a].[ArticleId]
      WHERE (([i].[ArticleId] = 1) AND ([a].[ArticlePrice] <= 1500)) 
      AND ((
          SELECT COALESCE(SUM([i0].[ArticleQuantity]), 0)
          FROM [InventoryArticle] AS [i0]
          INNER JOIN [Article] AS [a0] ON [i0].[ArticleId] = [a0].[ArticleId]
          WHERE ([i0].[ArticleId] = 1) AND ([a0].[ArticlePrice] < 1500)) > 10)

Expected result is one row. If number would be greater than 34, more rows should be added.

Nothing makes a question clearer than sample data and expected results. Please read [How to ask](https://stackoverflow.com/help/how-to-ask) and [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Stu, Dec 16 '21 at 21:49
Aside - `[delimiters]` only need to be used for reserved words or special characters, using them for *everything* just adds noise and makes a query unreadable; nothing in your query requires delimiting. — Stu, Dec 16 '21 at 21:50
Querry was generated by EF core, i did not write it, i only switched values for testing. I am not that good at SQL. Thank you for tips... — etaskin, Dec 16 '21 at 21:53
*Query was generated by EF core* - ah ok, makes sense! It looks very inneficient and unecessarily hitting the same tables twice which is not necessary, probably better to completely disregard and just clearly define the sample data and desired results & logic. — Stu, Dec 16 '21 at 22:04
Steps: Create a subquery which keeps a running sum of your quantity by your partition (article ID?) then using an outer query find the TOP 1 record > the desired quantity ordered by your runningtotal ascending. as far as how to get a a running total: https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server Now you could do this in a view or procedure and have a user pass in the article/qty desired to make it easier... — xQbert, Dec 16 '21 at 22:38
@xQbert Something like this if I am not wrong? https://www.db-fiddle.com/f/pdPTRGH6pZaofY2qFWuhdw/0 — etaskin, Dec 16 '21 at 23:16

score 2 · Accepted Answer · answered Dec 16 '21 at 23:41

You can use a windowed SUM to calculate a running sum ArticleQuantity. It is likely to be far more efficient than self-joining.

The trick is that you need all rows where the running sum up to the previous row is less than the requirement.

You could utilize a ROWS clause of ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. But then you need to deal with possible NULLs on the first row.

In any event, even a regular running sum should always use ROWS UNBOUNDED PRECEDING, because the default is RANGE UNBOUNDED PRECEDING, which is subtly different and can cause incorrect results, as well as being slower.

DECLARE @requirement int = 10;

SELECT
  i.InventoryArticleId,
  i.ArticleId,
  i.ArticleQuantity,
  i.InventoryId
FROM (
    SELECT
      i.*,
      RunningSum = SUM(i.ArticleQuantity) OVER (PARTITION BY i.ArticleId ORDER BY i.InventoryArticleId ROWS UNBOUNDED PRECEDING)
    FROM InventoryArticle i
    INNER JOIN Article a ON i.ArticleId = a.ArticleId
    WHERE i.ArticleId = 1
      AND a.ArticlePrice <= 1500
) i
WHERE i.RunningSum - i.ArticleQuantity < @requirement;

You may want to choose a better ordering clause.

EF Core cannot use window functions, unless you specifically define a SqlExpression for it.

T N · Answer 2 · 2021-12-16T23:40:32.167

My approach would be to:

Filter for the eligible records.
Calculate the running total.
Identify the first record where the running total satisfies your criteria.
Perform a final select of all eligible records up to that point.

Something like the following somewhat stripped down example:

-- Some useful generated data
DECLARE @Inventory TABLE (InventoryArticleId INT, ArticleId INT, ArticleQuantity INT)
INSERT @Inventory(InventoryArticleId, ArticleId, ArticleQuantity)
SELECT TOP 1000
    InventoryArticleId = N.n,
    ArticleId = N.n % 5,
    ArticleQuantity = 5 * N.n
FROM (
    -- Generate a range of integers
    SELECT n =  ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
    ORDER BY 1
) N
ORDER BY N.n

SELECT * FROM @Inventory

DECLARE @ArticleId INT = 2
DECLARE @QuantityNeeded INT = 500

;
WITH isum as (
    SELECT i.*, runningTotalQuantity = SUM(i.ArticleQuantity) OVER(ORDER BY i.InventoryArticleId)
    FROM @Inventory i
    WHERE i.ArticleId = @ArticleId
)
SELECT isum.*
FROM (
    SELECT TOP 1 InventoryArticleId
    FROM isum
    WHERE runningTotalQuantity >= @QuantityNeeded
    ORDER BY InventoryArticleId
) selector
JOIN isum ON isum.InventoryArticleId <= selector.InventoryArticleId
ORDER BY isum.InventoryArticleId

Results:

InventoryArticleId	ArticleId	ArticleQuantity	runningTotalQuantity
2	2	10	10
7	2	35	45
12	2	60	105
17	2	85	190
22	2	110	300
27	2	135	435
32	2	160	595

All of the ORDER BY clauses in the running total calculation, selector, and final select must be consistent and unambiguous (no dups). If a more complex order or preference is needed, it may be necessary to assign a rank value the eligible records before calculating the running total.

Thank you for your solution @TN, I will look it up and try it. — etaskin, Dec 16 '21 at 23:14

Get rows in SQL by summing up a until certain value is exceeded and stop retrieving

2 Answers2