1

So, I have two data extensions and I want to calculate count in 3rd data extension in each individual column.

My DE 1 (included all Emails of customers) My DE 2 (included all Emails and Status of customers)

DE 1

Email

DE 2

Email & Status

(Status column can have values like: Enabled, Disabled, OnHold and so on...there is a long list)

In my final 3rd DE, I am trying to do this with columns:

DE 3

Enabled_Count, Disabled_Count, OnHold_Count

(all fields are NULLABLE for testing purpose)

I want to calculate all counts in different columns and this is what I am trying:

SELECT Count(CASE WHEN c.Email IN (Select distinct Email from DE2 WHERE Status = 'Enabled') THEN 1 ELSE 0 END) AS Enabled_Count,
Count(CASE WHEN c.Email IN (Select distinct Email from DE2 WHERE Status = 'Disabled') THEN 1 ELSE 0 END) AS Disabled_Count,
Count(CASE WHEN c.Email IN (Select distinct Email from DE2 WHERE Status = 'OnHold') THEN 1 ELSE 0 END) AS OnHold_Count
FROM DE1 AS c

I am getting this error:

An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax.
Improve this question

1 Answer 1

Reset to default
1

I'd try an inner join instead:

select 
  sum(case when de2.status = 'OnHold'   then 1 else 0 end) as OnHold_Count
, sum(case when de2.status = 'Disabled' then 1 else 0 end) as Disabled_Count
, sum(case when de2.status = 'Enabled'  then 1 else 0 end) as Enabled_Count
from de1 
inner join de2 on de2.email = de1.email
Improve this answer
7
  • Thank you @adam-spriggs adding inner join seems to be working but i am not getting correct total. I have tried changing sum() to count() and the total count is not correct still. In my case, i am trying to count the total contacts (looks like sum function is only good to calculate numbers). In my production de, i need to add multiple conditions within each case and i am seeing incorrect counts. I am using multiple condition like this count(distinct case when de2.status = 'OnHold' and d2.comments <> 'test' then 1 else 0 end) as OnHold_Count
    – kuml
    Commented Jul 9, 2018 at 13:34
  • I changed the query like this and it seems to be working fine and giving correct count. count(distinct case when de2.status = 'OnHold' and d2.comments <> 'test' then de2.Email else null end) as OnHold_Count. this is working fine, just curious for your thoughts this change?
    – kuml
    Commented Jul 9, 2018 at 13:59
  • There's no way to validate your counts since you haven't provided any details about your data. I'd stick with sum().
    – Adam Spriggs
    Commented Jul 9, 2018 at 14:10
  • I apologize for the confusion. I did not provide any sample data as I though column names were self explanatory. I am looking here and it says SUM function is used to calculate the sum while in this case I am trying to find total count of records: https://www.codeproject.com/Questions/1005064/Difference-between-sum-and-count-in-sql-quer
    – kuml
    Commented Jul 9, 2018 at 14:17
  • The case statement returns a 1 or a 0, so the sum should count the total records for each case.
    – Adam Spriggs
    Commented Jul 9, 2018 at 14:29

You must log in to answer this question.

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