Why isn't this working? I took this query
SELECT s.TriggeredSendCustomerKey as TriggeredSend,
COUNT(o.TriggeredSendCustomerKey) as TotalOpens,
COUNT(case when o.IsUnique = 1 then 1 end) as UniqueOpens
FROM _Sent s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
AND s.TriggeredSendCustomerKey IN ('40712')
AND o.TriggeredSendCustomerKey IN ('40712')
Group by s.TriggeredSendCustomerKey
Which works for one triggered send perfectly. Whenever I add more TriggeredSendCustomerKeys in the parenthesis, the numbers are off. I also don't like hard coding the number so I tried this, which validates and seems like it should work, but the system doesn't like it.
SELECT s.TriggeredSendCustomerKey as TriggeredSend,
COUNT(o.TriggeredSendCustomerKey) as TotalOpens,
COUNT(case when o.IsUnique = 1 then 1 end) as UniqueOpens
FROM _Sent s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
Group by s.TriggeredSendCustomerKey