I have the following query and trying to find the percentage however the column consistently returns 0. Does anyone know the logic as to why and how I can rectify this. The column in question is Percentage.

The values received in my query is below:

select 
(v1.Total - v2.TotalUnits) as [Number Of Sales To Target], 
(v2.TotalUnits)  as v2t2,
v1.Total as v1T,
(v2.TotalUnits / v1.Total) as [Percentage]
from  --Change minus to a divide if you want %

table1 v1 Left Join table2 v2 ON v1.ID = v2.ID

I've tried casting as float and decimal but still didn't work.

CodePudding user response：

Based on this SO you can try something like

select 
(v1.Total - v2.TotalUnits) as [Number Of Sales To Target], 
(v2.TotalUnits)  as v2t2,
v1.Total as v1T,
((v2.TotalUnits*1.0) / v1.Total)*100 as [Percentage]
from  --Change minus to a divide if you want %

table1 v1 Left Join table2 v2 ON v1.ID = v2.ID

Notice v2.TotalUnits*1.0 also you will need to multiply by 100 to get the percentage

CodePudding user response：

If you divide an integer with another integer the result type will also be an integer. Example:

declare @a int = 7
declare @b int = 8

select @a/@b --Will return 0
select cast(@a as decimal)/cast(@b as decimal) --Will return 0.875