• Status: Solved
• Priority: Medium
• Security: Public
• Views: 656

# Select records based on column sum

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.

TIA...
0
jgv
1 Solution

Commented:
select top 1 row, quantity
from
(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}
0

Commented:
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.

Regards,

Patrick
0

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

SELECT * FROM
(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
0

Commented:
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.

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
ORDER BY PK

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

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

YourTable
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.

Bill
0

Author Commented:
It took a bit of tweaking but your jrb1's solution worked...one 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.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.