Data type conversions SQL

Question

I have two data extension (DE1 and DE2) with a boolean field called Subscribe, an email address field called EmailAddress and a text field called Campaign. I want to create a Query with the following functionality:

Imagine an scenario like this:

DE1:

EmailAddress - Subscribe - Campaign
a@example.com - True - C1
a@example.com - False - C2
b@example.com - False - C1
b@example.com - False - C2

What I want to do is, in DE1 for each EmailAddress and for each campaign the information within Subscribe field is FALSE change the status field in DE2 from True to False.

DE2:

EmailAddress - Status
a@example.com - True
b@example.com - FALSE

In order to know if all the fields are false, I've created a query like this:

SELECT
    ccall."Email Address", 
    'Unsubscribed' as Status,
    ccall.Domain,
    ccall.BounceCount,
    ccall."Subscriber Key",
    ccall.SubscriberType,
    ccall.SubscriberID
FROM ENT.DE1 ccall //This is a Shared Data Extension
JOIN ENT.DE2 ALLS ON //This is a Shared Data Extension
    ccall."Email Address" = ALLS.email
WHERE (ALLS.subscribe AS int)
    SUM (ALLS.subscribe)=0
GROUP BY ALLS.subscribe

but the following error appear:

An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'.

I don't know if maybe the problem is that I've written wrong the conversion from Boolean to Number or maybe there is something else wrong.

Answer 1

You'll need to use a HAVING clause for conditions involving aggregation.

And you can't use double-quotes for column names that contain spaces. Use brackets instead.

Also, you're missing a cast() in your GROUP BY and your SUM().

SELECT
  ccall.[Email Address]
, 'Unsubscribed' as Status
, ccall.Domain
, ccall.BounceCount
, ccall.[Subscriber Key]
, ccall.SubscriberType
, ccall.SubscriberID
FROM ENT.DE1 ccall
INNER JOIN ENT.DE2 ALLS ON (ccall.[Email Address] = ALLS.email)
GROUP BY cast(ALLS.subscribe as int)
HAVING SUM(cast(ALLS.subscribe as int)) = 0