Sum with conditions in snowflake-CodePudding

I'm trying to write a query using snowflake where I want to sum the total payment of a user who have multiple accounts (one email can create multiple accounts, yes) if at least one account is currently active, if so, then I want to sum up the total paid money whether the account is active or not -all payment history for that user-

Columns I'm using:

CustomerEmail

Active

Total_Paid

CodePudding user response：

Something like this?

select customeremail, sum(total_paid)
from test
where customeremail IN (select customeremail from test where active =true )
group by customeremail;

CodePudding user response：

Using HAVING and COUNT_IF:

SELECT customeremail, SUM(total_paid) AS total
FROM test
GROUP BY customeremail
HAVING COUNT_IF(active) > 0;