Converting varchar divide to a percentage

Transact-SQL
1

Good morning,

I'm trying to return the percentage in this query but I am either returning a 0 or a 1

Sum(Case When (localuserid = '-' And stationID = '-' And AssignedWorkGroup Like 'GBR%') Then 1 Else 0 End) / Sum(Case When a.CallId > '0' Then 1 Else 0 End) as "%"

So I tried this instead but I got the error "Operand data type varchar is invalid for divide operator."

FORMAT(('Abandoned'/'Inbound'),'P') as [Percentage]

Do I need to convert this to an INT, if so how do I do this in a CASE statement?

Full code below

Select	AssignedWorkgroup, 
		Sum(Case When a.CallId > '0' Then 1 Else 0 End) as "Inbound",
		Sum(Case When (localuserid = '-' And stationID = '-' And AssignedWorkGroup Like 'GBR%') Then 1 Else 0 End) as "Abandoned",
		Sum(Case When (localuserid = '-' And stationID = '-' And AssignedWorkGroup Like 'GBR%') Then 1 Else 0 End) / Sum(Case When a.CallId > '0' Then 1 Else 0 End) as "%", --Percentage Code attempt 1
		FORMAT(('Abandoned'/'Inbound'),'P') as [Percentage] -- Percentage Code attempt 2
From [I3_IC].[dbo].[calldetail_viw] a
Left Outer Join  I3_IC.dbo.Adv_SubWrapUpInfo b on a.CallId = b.InteractionIDKey
Left Outer Join  I3_IC.dbo.IR_RecordingMedia d on  a.callid = d.QueueObjectIdKey
Where Datediff(month, InitiatedDateTimeGMT,getdate()) = 1 and AssignedWorkgroup Not Like 'GBR DJ Test Call In' and
(assignedworkgroup like 'GBR%' or AssignedWorkGroup like 'IRL%') and assignedworkgroup Like '%Call In%' and a.calltype = 'External'
Group By AssignedWorkgroup
Order By AssignedWorkGroup

Thanks in advance

2

Change the THEN 1 ELSE 0 END to THEN 1.0 ELSE 0.0 END.
You are getting 0 because of integer division - i.e., when the numerator and denominator are zero, SQL Server divides and truncates the remainder.

You should also add a NULLIF(denominator,0) to avoid division by zero. Here denominator is the expression you now have in your code.

1 Like
3

Perfect, I can't believe it was as easy as that. When you say I need to add NULLIF, where do I need to add this to my code?

4

What I meant is this:

Sum(Case When (localuserid = '-' And stationID = '-' And AssignedWorkGroup Like 'GBR%') Then 1.0 Else 0.0 End) 
/ NULLIF(SUM(Case When a.CallId > '0' Then 1.0 Else 0.0 End) ,0)