How can I correct this SQL query?

Question

I am using a DB2 table. Each Customer has multiple fund balances stored in a column "Balance" and a column "As_of_date" to keep track of the date they've had that balance amount since. I need to return all customers that have had an aggregated balance of more than 1 million for the past 90 days.

I can't figure out how to do it. I am new in SQL

select Customer from MyTable
where As_of_date >= (current_date - 90 DAYS)
HAVING SUM(Balance) >= '1000000.00'

Answer 1

~It's not clear what database engine this is~ (oops: says v clearly DB2!) , or why there's capitals everywhere, but here is the answer in sql pseudocode, more or less:

select Customer, sum(Balance) as CustomerBalance
from Mytable
where As_of_date >= (current_date - 90 DAYS)
group by Customer
having CustomerBalance > 1000000

explanation:

• we want to select all the customers and all their balances,
• but we first filter out anything older than 90 days (the where as_of_date ... clause)
• the group by breaks down the data by customer, so for each customer, we take all their balances (for the last 90 days) and return a single value for each customer (the sum)
• then finally the having clause discards any rows of <Customer, CustomerBalance> that don't exceed a million.