SQL - How can I sum up a column after the results have been grouped and filtered in the having clause?

Question

Here is my current query: The objective is to find accounts that have received at least $500 in deposits within 30 days of their first deposit. Some accounts have been closed and re-opened, hence the first line of the 'WHERE' clause.

select      Deposits.accountNumber,
            min(Deposits.transDate) as "first deposit",
            Deposits.transDate,
            CAST(DATEADD(d,30,min(Deposits.transDate)) as date) as "30 days",
            sum(Deposits.amount) as "sum",
            Deposits.amount,
            Members.accountOpenDate
from        Deposits
inner join  Members on Deposits.accountNumber = members.accountNumber 
where       Deposits.transDate >= members.accountOpenDate
and         Deposits.accountNumber = 123456
group by    Deposits.accountNumber 
having      Deposits.transDate between min(Deposits.transDate) and DATEADD('d',30,min(Deposits.transDate))
and         sum(Deposits.amount) >= 500

The problem I am running into, is that the last line of the HAVING statement:

and         sum(Deposits.amount) >= 500

is including all of the transactions for the account, as if there was no 'HAVING' clause. It is factoring in transactions that are excluded from the first line of the 'HAVING':

having      Deposits.transDate between min(Deposits.transDate) and DATEADD('d',30,min(Deposits.transDate))

Here is what my data looks like (without grouping by account number):

accountNumber    amount    sum
123456           $100      $6,500
123456           $50       $6,500
123456           $50       $6,500

And here is what I am trying to get to:

accountNumber    amount    sum
123456           $100      $200
123456           $50       $200
123456           $50       $200

Thanks in advance. My DBMS is Intersystems-Cache. A link to their reference can be found Here.

Answer 1

You can try something like that:

select      filtered.accountNumber,
            min(filtered.transDate) as "first deposit",
            filtered.transDate,
            CAST(DATEADD(d,30,min(filtered.transDate)) as date) as "30 days",
            sum(filtered.amount) as "sum",
            filtered.amount,
            filtered.accountOpenDate
from        
(
    select * from Deposits
    inner join  Members on Deposits.accountNumber = members.accountNumber 
    where       Deposits.transDate >= members.accountOpenDate
    and         Deposits.accountNumber = 123456
    having      Deposits.transDate between min(Deposits.transDate) and DATEADD('d',30,min(Deposits.transDate))
) as filtered
group by    filtered.accountNumber 
having sum(filtered.amount) >= 500

With a query like that one you are first filtering your data applying the transDate condition then you can operate the filter on the sum of the amount