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

Improve this question
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
    Commented May 28, 2019 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
    Commented May 28, 2019 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
    Commented May 28, 2019 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
    Commented May 28, 2019 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
    Commented May 28, 2019 at 14:22

1 Answer 1

Reset to default
3

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)

Improve this answer

You must log in to answer this question.

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