CODEWITHSUNDEEP
sqlsql-server
user8834780
user8834780

Reputation: 1670

Why doesn't the "having" clause work?

For the following query:

select count(distinct email_address) 
from 
(
select distinct email_address, 
case when elq_activity_type='EmailSend' then 1 else 0 end 'Sends', 
case when elq_activity_type='Bounceback' then 1 else 0 end 'Bounces', 
case when elq_activity_type='EmailOpen' then 1 else 0 end 'Opens', 
case when elq_activity_type='EmailClickthrough' then 1 else 0 end 'Clicks' 
from elq_stg_activity
) a
having sum(sends-bounces)>0

The having clause doesn't seem to be doing anything. What am I doing wrong? Need to get all unique emails that had an email delivered to them (send-bounce). Thanks!

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want this:

select count(email_address) 
from (select email_address, 
             sum(case when elq_activity_type = 'EmailSend' then 1 else 0 end) Sends, 
             sum(case when elq_activity_type = 'Bounceback' then 1 else 0 end) as Bounces, 
             sum(case when elq_activity_type = 'EmailOpen' then 1 else 0 end) as Opens, 
             sum(case when elq_activity_type = 'EmailClickthrough' then 1 else 0 end) as Clicks 
      from elq_stg_activity
      group by email_address
     ) a
where sends = bounces;

There are numerous issues with your query. This is the only sensible interpretation I could think of.

Upvotes: 1

Related Questions