SQL query count issue - attempting to only see multiple results over 0-CodePudding

Having issues with a SQL query. I am attempting to select the account number, amount, and created date in a database. For that set, I am attempting to see all amounts over $0, within the last 7 days, and only accounts with over 3 instances of charges over $0 within the last 7 days. I've tried a few different things, but the issue I am having is how to build in the over three instances criteria. Any ideas?

select
   account,
   count(account),
   amount,
   created
  from account_charges
  where account_charges.amount > 0 and date(account_charges.created) >= date_add('day', -7, date(data_load_time)) 
  group by
   account_charges.account having count(*) > 3,
   account_charges.amount,
   account_charges.created

CodePudding user response：

Looks to be a simple copy-past error. Does moving the Having statement to the end work out better?

select
   account,
   count(account),
   amount,
   created
  from account_charges
  where account_charges.amount > 0 and date(account_charges.created) >= date_add('day', -7, date(data_load_time)) 
  group by
   account_charges.account,
   account_charges.amount,
   account_charges.created
  having count(*) > 3

Here is a variation you may want to consider. The query above will only return accounts that have the same amount that is greater than zero more than three times. Perhaps you want to count all amounts above zero by account instead?

 select
   account,
   count(*),
   max(amount) as max_amount,
   min(created) as created
  from account_charges
  where account_charges.amount > 0 and date(account_charges.created) >= date_add('day', -7, date(data_load_time)) 
  group by
   account_charges.account
  having count(*) > 3