Need to show Largest Purchase and the date and Last purchase/date and it is not correct

Question

I am only working with two tables.

One is a list of IDs (Accounts that i need to find the information on)

I am looking up those ids in another table (purchases)

The purchase table has a list of every purchase the amount and the date of the purchase.

I need to know the most recent purchase and the date of that purchase and the largest purchase and the date of that largest purchase and the sum of all purchases.. All where the purchase amount is greater than zero. I manage to get this far. But when I try to add anything else the info is wrong.

the FORSPRING table all will have records (most likely multiple) in the purchases tables

`SELECT FORSPRING.ACCOUNTID,MAX(p.purchase_date__c) "LAST PURCHASE DATE"
FROM FORSPRING        
LEFT JOIN PURCHASES__c P
WHERE p.amount__c>0
ON FORSPRING.ACCOUNTID=G.ACCOUNT__C
GROUP BY FORSPRING.ACCOUNTID
ORDER BY "LAST PURCHASE DATE";`

Answer 1

I think you are looking for a subquery instead.

SELECT FORSPRING.ACCOUNTID, (SELECT MAX(purchase_date__c) FROM PURCHASES__c WHERE o.amount__c>0 AND FORSPRING.ACCOUNTID=G.ACCOUNT__C GROUP BY ACCOUNT__C) "LAST PURCHASE DATE" FROM FORSPRING GROUP BY FORSPRING.ACCOUNTID ORDER BY "LAST PURCHASE DATE";