Query to show life cycle revenue?

I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table and a sales table joined by custno.

SELECT Customer.LastName, Sales.InvDate, Sales.AmtCharge
FROM Customer INNER JOIN
Sales ON Customer.CustNo = Sales.CustNo

The output I'd like is

CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount

Getting the first inv date seems straight forward

SELECT

Customer.CustNo,MIN(Sales.InvDate)AS FirstInvFROM CustomerINNERJOINSalesON Customer.CustNo= Sales.CustNoGROUPBY Customer.CustNo

However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head.

Can anyone point me in the right direction?

Please give this one a try:

SELECT a.CustNo, a.LastName, b.AmtCharge AS FirstInvoiceAmount, a.LifeCycleAmount,(a.LifeCycleAmount-b.AmtCharge) AS LifeCycleAmountWithouttheFirst FROM (SELECT Customer.CustNo, Customer.LastName, SUM(Sales.AmtCharge) AS LifeCyleAmount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNoGROUPBY Customer.CustNo,Customer.LastName) AS a INNER JOIN ( SELECT Customer.CustNo,MIN(Sales.InvDate)AS FirstInv, Sales.AmtChargeFROM CustomerINNERJOINSalesON Customer.CustNo= Sales.CustNoGROUPBY Customer.CustNo,Sales.AmtCharge) AS b ON a.CustNo=b.CustNo

SELECT a.CustNo, a.LastName, b.AmtCharge AS FirstInvoiceAmount,
a.LifeCycleAmount, (a.LifeCycleAmount-b.AmtCharge)
AS LifeCycleAmountWithouttheFirst FROM
(SELECT Customer.CustNo, Customer.LastName,
SUM(Sales.AmtCharge) AS LifeCycleAmount FROM
Customer INNER JOIN Sales
ON Customer.CustNo = Sales.CustNo
GROUP BY Customer.CustNo, Customer.LastName)
AS a INNER JOIN (SELECT c.CustNo, c.minInvDate, d.AmtCharge FROM (SELECT Sales.CustNo, MIN(Sales.InvDate) AS minInvDate FROM Sales
GROUP BY Sales.CustNo) AS c INNER JOIN Sales d ON c.CustNo=d.CustNo AND c.minInvDate=d.InvDate) AS b ON a.CustNo=b.CustNo

But if a customer had two sales on the first day, you may need another column to get just the first one.

|||That worked perfectly for what I wanted thanks so much!