CODEWITHSUNDEEP
sqlms-accesssum
Ultratapir
Ultratapir

Reputation: 23

SQL finding the sum of subrange of values

I am working on a conversion script in Access VBA where i need a query to change the column requested quantity to contain the sum of the delivered quantity per order line. Right now it looks like this:

ordernumber  orderlinenumber  requestedquantity  deliveredquantity
123456       1000             1                  1
123456       1001             2                  2
123456       2000             50                 50
123456       3000             10                 10

So for order number 123456 line number 1000 & 1001 requested quantity should become 3.

Basically what i need is an update query that goes through order line 1000-1999, calculates the sum of the delivered quantity and sets it for the requested quantity. I tried a few different constructions using the SUM clause but i can not seem to find a way to loop through the order lines.

Upvotes: 2

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Hmmm . . . You can use aggregation

select ordernumber, min(orderlinenumber), sum(requestedquantity), sum(deliveredquantity)
from t
group by ordernumber, orderlinenumber \ 1000

The backwards slant is MS-Access-speak for integer division.

Upvotes: 2

Related Questions