sqlms-accessdifference-between-rows

ACCESS/SQL: Calculating the difference between rows (not dates)


I looked through the questions here but didn't find one that suited my case.

I'm trying to write a query that will output the difference between rows

Here is a table:

ITEM     CYCLES
--------------------
itemA     5
itemA     17
itemA     20  
itemA     22
itemB     26
itemB     30
itemB     37

it is actually obtained by a query, and with an order by (item, cycles)

here is what I'd like the query to give me:

itemA 12
itemA 3
itemA 2
itemB 4
itemB 7

I have absolutely no idea how to proceed in SQL. Is it even possible ? or do I have to write a function ?

*****************************EDIT*********************************

I appologize for the lack of precision, and even some absurd mistakes. I was rushing out and wrote hastily :/
I'm analyzing item failures, and need to output cycles between failures.

And actually looking at it today I don't understand why I put that middle column (A,B,C...) which I don't have in my table.
And indeed, I don't need to output zero values, but there shouldn't be any anyway. I'll try the provided solutions and will get back; thanks for the answers !


Solution

  • Here's an updated solution, based on the changes to your question. Note that you'll need to change Qry to the name of your query:

    SELECT Qry.Item, Qry.Cycles - (SELECT TOP 1 Cycles FROM Qry AS Q 
                                   WHERE Qry.Item=Q.Item
                                     AND Q.Cycles < Qry.Cycles 
                                   ORDER BY Q.Cycles DESC) AS Diff
    FROM Qry
    WHERE (SELECT TOP 1 Cycles FROM Qry AS Q 
           WHERE Qry.Item=Q.Item AND Q.Cycles < Qry.Cycles) Is Not Null
    ORDER BY Qry.Item, Qry.Cycles;
    

    This produces the following output:

    Item    Diff
    itemA   12
    itemA    3
    itemA    2
    itemB    4
    itemB    7
    

    I assume that the 6 in your sample output was a typo, since 30 - 26 = 4.