SQLTeam.com | Weblogs | Forums

SQL Query Help

Hi,
It's been a very long time since I've used SQL and I need to combine the below 4 queries into 1.
So my output will have 4 columns: Email_Count_SENT, Email_Count_OPEN, Email_Count_CLICK & Email_Count_BOUNCE.
They are all 4 pulling from the same main table: [TABLE_SENT_T1], but have different filters.

Here are the 4 selects:

Select Count(Distinct(Email_Address)) as Email_Count_SENT
from [TABLE_SENT_T1]

count(distinct(Email_Address)) as Email_Count_OPEN
from
(select Email_Address, OpenEvent
from [TABLE_SENT_T1]
group by Email_Address, OpenEvent
having OpenEvent is not null
) t1

count(distinct(Email_Address)) as Email_Count_CLICK
from
(select Email_Address, ClickEvent
from [TABLE_SENT_T1]
group by Email_Address, ClickEvent
having ClickEvent is not null
) t1

count(distinct(Email_Address)) as Email_Count_BOUNCE
from
(select Email_Address, BounceEvent
from [TABLE_SENT_T1]
group by Email_Address, BounceEvent
having BounceEvent is not null
) t1

Thanks!

This should do it:

Select Count(Distinct Email_Address) as Email_Count_SENT
,count(Distinct CASE WHEN OpenEvent IS NOT NULL THEN Email_Address END) as Email_Count_OPEN
,count(Distinct CASE WHEN ClickEvent IS NOT NULL THEN Email_Address END) as Email_Count_CLICK
,count(Distinct CASE WHEN BounceEvent IS NOT NULL THEN Email_Address END) as Email_Count_BOUNCE
from [TABLE_SENT_T1]

Thanks Robert,
Yes that did the trick!