Subtracting Two Columns In SQL After Join

I have one table called Orders

PurchaseID VARCH开发者_如何学PythonAR
purchaseDate DATETIME
purchasePrice FLOAT

I want to find the difference between the purchase price on one day versus another day - The puyrchaseID will be the same.(This is just an example table of course)

SELECT a.purchasePrice AS purchasePriceDay1 ,  b.purchasePrice AS  purchasePriceDay1
from Orders a , Orders b
where a.PurchaseID = b.PurchaseID

Would that actually work

SELECT 
    a.PurchaseID, 
    ABS(a.PurchaseID - b.PurchaseID) AS diff
FROM 
    PurchaseID a INNER JOIN PurchaseID b ON a.PurchaseID=b.PurchaseID
WHERE a.PurchaseID=? 
    AND a.purchaseDate=? 
    AND b.purchaseDate=?

Fill in the placeholders with the ID and dates to compare.

Consult your DBMS documentation to see if you have the math absolute function.

You'd probably also want to add AND a.purchaseDate <> b.purchaseDate

have you tried something like

SELECT a.purchasePrice AS p1,  b.purchasePrice AS  p2, a.purchasePrice - b.purchasePrice as difference
from Orders a inner join Orders b on a.PurchaseID = b.PurchaseID

SELECT ((SELECT purchasePrice FROM Orders WHERE PurchaseID = 1 AND purchaseDate = '2010-06-21') - (SELECT purchasePrice FROM Orders WHERE PurchaseID = 1 AND purchaseDate = '2010-06-20')) AS diff; 

You can perform the subtracting right in the query as well if you want to. You probably also want to make sure you don't get two purchases from the same day:

SELECT a.purchasePrice AS purchasePriceDay1 ,  b.purchasePrice AS  purchasePriceDay2, (purchasePriceDay2-purchasePriceDay1) AS purchasePriceDelta
from Orders a , Orders b
where a.PurchaseID = b.PurchaseID
and a.PurchaseDate < b.PurchaseDate