I'm fairly new with SQL. I have 2 data extensions. DE1 is a list of email addresses and changes each day. DE2 is for counting how many times each address appears in DE1.

So, my question is, how would I create a SQL query that follows this logic:

  • For each email address in DE1
  • If exists in DE2, count = count + 1
  • If doesn't exist in DE2, create new row adding email address and count = 1

I hope this makes sense. I appreciate any help you can provide. Thanks!


Something like:

SELECT a.emailaddres,
CASE WHEN b.emailaddress IS NOT NULL THEN b.Count + 1 ELSE 1 END as Count
FROM [DE1] a
ON a.emailaddress = b.emailaddress

should do it for you. Might need to tweak it a bit as I had to do it off the cuff.

This requires:

  1. DE1 to have field emailaddress
  2. DE2 to have a Count field and an emailaddress field to match against.
  • This is a great start, thank you. I was able to get the query working using your code as a guide, and it's really close. If the email address already exists in DE2, it correctly adds +1 to the count column, but if the email address does not exist, it does nothing. I need it to add a new row when the email address doesn't exist in DE2.
    – Dan Claude
    Jul 19 '18 at 17:18
  • sorry misunderstood. My update to FULL OUTER JOIN should solve that issue. Jul 19 '18 at 17:24
  • It turns out it almost works. When DE2 is empty and I run the SQL query, it correctly adds all of the new email addresses from DE1 to DE2 and properly puts a 1 in the counting column. BUT, if there are already existing rows in DE2, it does not add new rows. It only updates the matching email address rows by correctly adding +1. So, my problem is it only adds new rows when DE2 is completely empty. I have the data action set to Update, because that seems like the correct choice, but I have tried all 3.
    – Dan Claude
    Jul 23 '18 at 19:29
  • What is the target DE and Data Action? Are you targeting DE2? Jul 23 '18 at 19:32
  • Data Action is Update, and yes. I am targeting DE2.
    – Dan Claude
    Jul 23 '18 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.