I have a table that contains donation history (id, transdate, amt to simplify) and I need to calculate some totals. Some of them I can get using the max and min functions but others I'm having trouble pulling out. What I need is:
Number of donations
Sum of all donations
First Donation Date
First Donation Amt (need help)
Last Donation Date
Last Donation Amt (need help)
Largest Donation Date (need help)
Largest Donation Amt
I have everything except the ones that say need help.
Here is a simplified table
CREATE TABLE [dbo].[Donations]( [ID] [varchar](10) NULL, [TransactionDate] [datetime] NULL, [Amount] [money] NULL, ) ON [PRIMARY]
Here is some data
insert into Donations Values('12','3-21-2009',25) insert into Donations Values('12','3-10-2010',25) insert into Donations Values('12','11-21-2012',100) insert into Donations Values('12','5-25-2013',150) insert into Donations Values('12','6-15-2014',150) Insert into Donations Values('17','1-1-2014',500) insert into Donations Values('26','12-23-2007',100) insert into Donations Values('26','5-22-2013',150) insert into Donations Values('26','2-11-2015',500)
Here is the query I'm using
SELECT ID, COUNT(ID) AS Donation_count, SUM(Amount) AS SumofDonations, MIN(TransactionDate) AS FirstDonation_date, MAX(TransactionDate) AS LastDonation_date, MAX(Amount) AS LargestDonation_Amount FROM dbo.Donations GROUP BY ID
Here are my results so far
Can anyone help me pull the First Donation Amt (corresponding to First donation Date), Last Donation Amt(corresponding to Last donation Date), and Largest Donation Date (corresponding to Largest Donation Amt)?
Thanks in advance for your help.