1

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!

Improve this question

1 Answer 1

Reset to default
1

Something like: 

SELECT a.emailaddres,
CASE WHEN b.emailaddress IS NOT NULL THEN b.Count + 1 ELSE 1 END as Count
FROM [DE1] a
FULL OUTER JOIN [DE2] b
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.
Improve this answer
8
  • 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
    Commented Jul 19, 2018 at 17:18
  • sorry misunderstood. My update to FULL OUTER JOIN should solve that issue.
    – Gortonington
    Commented Jul 19, 2018 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
    Commented Jul 23, 2018 at 19:29
  • What is the target DE and Data Action? Are you targeting DE2?
    – Gortonington
    Commented Jul 23, 2018 at 19:32
  • Data Action is Update, and yes. I am targeting DE2.
    – Dan Claude
    Commented Jul 23, 2018 at 19:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .