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.
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 | |
  • 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 Jul 9 '18 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 Jul 9 '18 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 Jul 9 '18 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 Jul 9 '18 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 Jul 9 '18 at 14:29

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.