devxlogo

Calculating Running Totals

Calculating Running Totals

Question:
[Joe Celko’s Quantity on Hand Puzzle]

You are now the guy in charge of the company inventory. You get requisitions that tell how many widgets people are putting into a bin or taking out of it on a given date. Sometimes the quantity is positive (returns), sometimes the quantity is negative (withdrawals).

    CREATE TABLE Requistions        (reqdate DATE NOT NULL,        qty INTEGER NOT NULL CHECK (qty <> 0)        );
Your job is to provide a running balance on the quantity on hand as an SQL column. We want something like:
    Inventory    reqdate            qty       qty_on_hand    ========================================    1993-11-01         100            100    1993-11-02         120            220    1993-11-03        -150             70    1993-11-04          50            120    1993-11-05         -35             85

Answer:
This is really a quickie with the SQL-92 feature of using a scalar query in the SELECT list.

    SELECT reqdate, qty,             (SELECT SUM(qty) FROM Inventory AS S2                WHERE S2.reqdate <= S1.reqdate) AS qty_on_hand        FROM Inventory AS S1        ORDER BY reqdate;
If the innermost query comes back empty, it is a NULL. This interesting feature of the SQL-92 standard sometimes — not always! — lets you write an OUTER JOIN as a query within the SELECT clause. For example, this query will work only if each customer has one or zero orders:
    SELECT custno, custname,             (SELECT orderamt                 FROM Orders                 WHERE Customers.custno = Orders.custno)        FROM Customers;
and give the same result as
    SELECT custno, custname, orderamt        FROM Customers LEFT OUTER JOIN Orders             ON Customers.custno = Orders.custno;

Puzzle provided courtesy of:
Joe Celko
[email protected]

See also  Redefining Energy Management: Advanced Solutions for Modern Buildings
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist