1

I'm trying to create a SQL Query which counts the same values in a Data extensions and Groups them by a Unique Value.

Usecase: My data extension looks like this:

enter image description here

I want to see how many email addresses have used the same UniqueCode. Currently I have the following SQL running, but somehow he doesn't show me the count of a UniqueCode:

    SELECT COUNT(distinct email) as test1 , UniqueCode AS test2
  FROM DataExtension
  GROUP BY UniqueCode

The target Data Extension does contain the email and UniqueCode field, but I only receive the UniqueCodes or email.

Can someone help me?

Here is a screenshot on how I have it in mind:

enter image description here

7
  • Can an email have the same uniquecode multiple times or only once? And would you like to count one email with the same uniquecode only once or x-times? – Johannes Schapdick May 28 '19 at 13:39
  • An email can only exist once in the data extension, but the UniqueCode can exist multiple times. So for example, you will see for code 11111 a count of 3 and for code 11122 a count of 2. I've added a screenshot on how I would like to see it. – Daan Kramer May 28 '19 at 13:46
  • 2
    To be honest i think its just SELECT COUNT(*) as UniqueCodeCount, UniqueCode FROM DataExtension GROUP BY UniqueCode or SELECT COUNT(UniqueCode) as UniqueCodeCount, UniqueCode FROM DataExtension GROUP BY UniqueCode or however you want those column to be named (called it UniqueCodeCount) – Johannes Schapdick May 28 '19 at 13:58
  • Somehow its weird that you name the column of the count email(primary key). If you want to have the emails in it you cannot group by here but need to partition. I think its not 100% clear for me because the screenshot of the target dataextension is damn confusing – Johannes Schapdick May 28 '19 at 14:07
  • To be honest your query must error or even fail the syntax check, you cannot use fields which are not part of the group by clause. If thats the case this information would have been kind of useful. – Johannes Schapdick May 28 '19 at 14:22
2

Now as answer (that the question becomes solved and has an answer). You cannot use fields which are not part of the group by clause.

To be honest i think its just SELECT COUNT(*) as UniqueCodeCount, UniqueCode FROM DataExtension GROUP BY UniqueCode or SELECT COUNT(UniqueCode) as UniqueCodeCount, UniqueCode FROM DataExtension GROUP BY UniqueCode or however you want those column to be named (called it UniqueCodeCount)

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.