Select records based on column sum

Posted on 2006-07-11
Last Modified: 2012-08-13
Need a sql statement where I could specify a value and return only enough records where the quantity satisfies the requirement.

Row      Quantity
A      4
B      2
C      5
D      5
E      12

If I needed a quantity of 10 then I would want rows A, B and C (4 + 2 + 5 = 11 which satisfies the requirement of 10).
If I needed a qty of 13 then I would want rows A, B, C and D (4 + 2 + 5 + 5 = 16)

I know I could use a cursor for this but would really like to do this within a single select statement.

Question by:jgv
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 25

Accepted Solution

jrb1 earned 500 total points
ID: 17086655
select top 1 row, quantity
(select a.row, a.quantity, sum(b.quantity) as ttlqty
from yourtable a
   , yourtable b
where b.row <= a.row
group by a.row, a.quantity)
where ttlqty > {yournumber}
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17086780
Hi jgv,

You're not going to be able to use a simple SELECT statement to do this.  Your best bet is probably to use a
stored procedure that tests this out.


LVL 17

Expert Comment

ID: 17087104
If your table have Primary Key(PK), try this

(select Row,(select SUM(Quantity) as c from yourtable as a where a.PK<yourtable.PK) as c from yourtable) AS t1
where c>10

Hope this help
LVL 19

Expert Comment

ID: 17087259
Let's break this down.

First, can you get a list of the Last Item and the sum of it and it's predicessors values?  Once you do that, it is a simple matter of selecting that item and it's predicessors.

What you want to start with is a result set like:
A    4
B    6
C    11
D    16
E    28

There are a couple of ways to do this.  The first, as eluded to by HuyBD is to join each row in the table to itself and every other predicessor row.  This gets to be expensive in a hurry if you have a lot of items.  If there are only a few dozen, who cares; it is just a couple of million machine cycles.  Let's assume the problem is larger and you want the best performance.

The slick way to do this is with a temp table and a tricky update statement.  First create a temp table with an extra column.  Then do the tricky update, then you can answer the questions out of that temp table, at least until you update the base table.

-- STEP 1:
SELECT PK, Quantity, CONVERT(INT,0) AS RunningTotal, CONVERT(INT,0) as RunningMin
   INTO #T
FROM YourTable

-- STEP 2:
DECLARE @RunningTotal INT
SET @RunningTotal = 0
     SET RunningTotal = @RunningTotal + Quantity,
            RunningMin = @RunningTotal + 1,
            @RunningTotal = @RunningTotal + Quantity

-- STEP 3 - n:
SELECT PK, Quantity
   FROM #T
 WHERE RunningMin < @DesiredValue

A     4
B     2
C     5
D     5
E     12

#T after creation
A     4      4      1
B     2      6      5
C     5    11      7
D     5    16     12
E    12    28     17

Query for the rows necessary to add up to 10...  WHERE RunningMin <= 10 and you get
A, B, and C

Mock it up and let us know how it goes.

LVL 12

Author Comment

ID: 17087394
It took a bit of tweaking but your jrb1's solution inline statement to pull only the records that satisfy the requirement

Bill, I hear you on the performance end but in this case the trade off is worth it.


Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question