Unable to get percentage value in SQL Marketing cloud

Question

Everyone

The below query is working fine without bounce_rate condition. When I am adding the bounce_rate it is throwing an error. Anyone please suggest on this.

SELECT
j.emailname, j.emailsubject, s.JobID ,TotalSends ,TotalBounces ,(TotalSends - TotalBounces) AS TotalDelivered, CAST((CAST(TotalBounces AS FLOAT)/CAST(TotalSends AS FLOAT))*100 AS FLOAT) AS Bounce_RATE

FROM

(SELECT
JobID
,MAX(row) as TotalSends
FROM
    (SELECT
    JobID ,row_number()over(partition by JobID order by SubscriberKey ASC) as row
    FROM _Sent ) 

f GROUP BY f.JobID) s

INNER JOIN 

(SELECT
JobID
,MAX(row) as TotalBounces
FROM
    (SELECT
    JobID ,row_number()over(partition by JobID order by SubscriberKey ASC) as row
    FROM _Bounce ) 
d GROUP BY d.JobID) b

ON s.JobID = b.JobID Inner join _job j on s.jobid = j.jobid

Answer 1

Marketing Cloud Query Studio doesn't like CAST in SELECT. You are getting an error there, as it interprets AS in CAST to be aliasing AS. It should work, however, in the Query Activity of Automation Studio.

Regarless, you can make it work in both places by changing CAST to CONVERT:

CONVERT(FLOAT,(CONVERT(FLOAT,TotalBounces)/CONVERT(FLOAT,TotalSends))*100) AS Bounce_RATE

Depending on the use case, you might consider using the added power of the CONVERT by defining precision and scale:

CONVERT(DECIMAL(3,2),(CONVERT(DECIMAL(3,4),TotalBounces)/CONVERT(DECIMAL(3,4),TotalSends))*100) AS Bounce_RATE

Example output will be 92.45 instead of 92.4482 with the FLOAT.

You can check out more about CAST vs CONVERT and SFMC limitations in my docs.