Query total number of sends

Question

In ExactTarget I am trying to query the total number of sends within the last 7 for each subscriber, and only store the results where the number of 'TotalSends' is greater than 0.

Here is what I have going so far:

SELECT  EmailAddress, (
  SELECT count(*) FROM _Sent p
  WHERE  p.SubscriberKey = s.EmailAddress 
  AND EventDate between DATEADD(day, -7, CURRENT_TIMESTAMP) 
  AND CURRENT_TIMESTAMP
) 
AS 'TotalSent' 
FROM Master_Subscriber_DE AS s
WHERE 'TotalSent' > 0

I run this query and get no results. If I remove that last line - It works, but I get ALL results (I only want the results where 'TotalSent' > 0)

Answer 1

I ended up getting what I needed with the code below:

SELECT count(*) AS TotalSent, p.SubscriberKey as EmailAddress
FROM _Sent p 
WHERE p.EventDate BETWEEN DATEADD(day, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY p.SubscriberKey
HAVING COUNT(*) > 0

Answer 2

Haven't test it, but something like this should work assuming all your subscriberkeys are email addresses

SELECT SubscriberKey, COUNT(SubscriberKey) as TotalSent FROM _Sent
WHERE EventDate between DATEADD(day, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY SubscriberKey
HAVING COUNT(SubscriberKey) > 0

Answer 3

Try using a Having clause:

SELECT count(*) as TotalSent, s.EmailAddress FROM _Sent p
  WHERE  p.SubscriberKey = s.EmailAddress 
  AND EventDate between DATEADD(day, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
group by s.EmailAddress
having count(*) > 0