Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

SELECT DISTINCT on Campaign V7

Avatar

Level 4

I need to now the unique elements of the column: DESCORREOAF from Recipients table. 

I used this query:

SELECT COUNT(DISTINCT DESCORREOAF) AS DESCORREOAF FROM NmsRecipients;

But is giving error: 

27/09/2023 17:28:54 PGS-220000 PostgreSQL error: ERROR: relation "nmsrecipients" does not exist LINE 1: ...T COUNT(DISTINCT DESCORREOAF) AS DESCORREOAF FROM NmsRecipie... ^ .

The column I need to query is this in XML: <attribute name="DESCORREOAF" type="string"/>

ogonzalesdiaz_0-1695853933938.png

ogonzalesdiaz_1-1695853976653.png

 



 



Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 3

Hi @ogonzalesdiaz ,

As your data type is string, you need to give 's' in prefix of the attribute name while running the SQL code.
Please try the below SQL code and execute it - it will work!

SELECT COUNT(DISTINCT sDESCORREOAF) AS DESCORREOAF FROM NmsRecipients;

Thanks,
Sanchari

View solution in original post

3 Replies

Avatar

Community Advisor

Hello @ogonzalesdiaz 

 

If you are doing some testing and want to know the numbers, You can use the distribution of values functionality.

You can use the query activity, add the column, and click the " don't he highlighted " icon. It will give you the count and distinct values.

Manoj_Kumar__0-1695860497558.png

 

 

 

If you want these records in a workflow, then you can do this

  • Use query activity and pull all the records by condition id > o
  • Then click "Add data" and click the Next button 2 times.
  • Then select the output columns like this: Replace the Age column with your column.
    Manoj_Kumar__1-1695860724428.png

     

  • Click on save.
  • Then click on edit additional data to disable the adding of primary keys.
    Manoj_Kumar__2-1695860839972.png

     

 


     Manoj
     Find me on LinkedIn

Avatar

Correct answer by
Level 3

Hi @ogonzalesdiaz ,

As your data type is string, you need to give 's' in prefix of the attribute name while running the SQL code.
Please try the below SQL code and execute it - it will work!

SELECT COUNT(DISTINCT sDESCORREOAF) AS DESCORREOAF FROM NmsRecipients;

Thanks,
Sanchari

Avatar

Community Advisor

Hi @ogonzalesdiaz ,

 

Please check the values of SQL Table name with name "sqltable" and also the SQL Column name "sqlname" from the Preview Tab of Data Schema and try running the SQL Code .

 

In your Example, the SQL Code can be as shown below:

 

SELECT COUNT(DISTINCT {sqlname to be checked in Preview Tab in Recipient Schema}) AS DESCORREOAF FROM NmsRecipient;

 

Regards,

Pravallika.